SSIS – Pulling Data from a non default collation DB to a default collation DB

So, you install SQL, and if you are in the US, you 99.9999% of the time install it with the default collation, or language, or whatever. US English, Codepage 1252, etc, etc Latin blah blah…

Then, you are tasked with pulling data from somewhere else, but it happens to be a different language or collation than your destination, what do you do?

In SSIS, there is a setting you can change on your OLEDB source and OLEB destination to make sure it works. The default codepage is 1252, but if you try pulling data from a non default DB, it will error out all over the place. If you change the setting “AlwaysUseDefaultCodePage” from false (the default) to true, on both your source and destination, then it should work.

Another thing to assist with this once you have a bunch of SSIS packages with these non-default settings changed (not just the one I describe above, but any setting) – if you install BIDs Helper, and then right click on the package, you can get a non-default setting report, which is pretty awesome actually, especially if you inherit any packages and want to know quickly what default settings have been tweaked.