SSIS Task 'Transfer SQL Server Objects Task' does NOT allow collation
change. The 'Use Collation' option just prevents data loss if the
source and target columns have different collations. If you copy the
schema as well as the data it will use the original collation.
So the standard option is to script everything out without collations,
run script on a new database with the new collation and then import
the data using bcp/SSIS etc. This leads to problems with old
unparseable or unexecutable stored procedures and old orphaned user
accounts, so some data cleanup may be required.
I have come across some script online that just changes the character
columns' collation using ALTER TABLE statement while dropping and
recreating all dependencies.
Assuming this works (from MVP blog, but will be testing!), I just
wanted to check whether there was any possiblility of data loss when
using the ALTER TABLE statement to change collation on a populated
column from sql_latin1_general_cp1_ci_as to latin1_general_ci_as?