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.
Just trying to make it all work... and then stay that way 