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 background info is at

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


Leave a Reply

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

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

Facebook photo

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

Connecting to %s