Column ‘Mycol’ cannot convert between Unicode and non-Unicode when exporting simple tables to Excel in SSIS

I’m sure that anyone who has used SQL Server Integration Services (SSIS) for more than a day has hit this really annoying ‘feature’. The problem is basically that the source / destination of MS Excel in SSIS defines all strings as Unicode. If the table you are importing to or exporting from has non-unicode (e.g. varchar vs nvarchar) fields then you’ll see error, after error saying “cannot convert between Unicode and non-unicode…”. The solution is to use a Data Conversion transformation however this is extremely tedious especially as it changes the name of every column and requires click after click after click to do something which frankly should be implicit or at worst automated.

So we end up in a situation like this:

 

Luckily Todd McDermid has written a component called “Replacing Data Conversion” that can automate the conversion for us, this is easy to configure:

 

Four clicks later we have a happy script:

Download at http://rdc.codeplex.com/ background info is at http://toddmcdermid.blogspot.co.uk/2010/08/convert-several-columns-in-ssis-with.html

Cheers Todd, you’ve saved me a lot of tedious work.

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