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.