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.

image

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s