I have a dimension to load which has a field called description with a data type of VARCHAR(50). Its collation is SQL_Latin1_General_CP1256_CS_AS and it contains Arabic data such as "مركز العقبة". Its source has the same type, size and collation, but every time I load the dimension this field gets updated! Why does this happen?
You should make sure that the collation of both your database and the tables is the same. Specifically you should set them both to Arabic_CI_AI_KS_WS as your comment suggests.