I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error stating Output column and column "A" cannot convert between unicode and non-unicode string data types.
To fix this I added a data conversion component and converted all the columns to
"Unicode String [DT_WSTR]"
and I no longer received the error. The only problem is that I had about 50 columns where I had to go 1 by 1 and select "Unicode String [DT_WSTR]" from the drop down list. I then had to go into the destination component and map the newly converted columns to my excel file.
My question is, if anyone else has come across this, is there a better more efficient way to get around having to do all the manual data type conversions? Having to convert and map all the columns one by one doesn't seem to practical especially if you have a large number of rows.
I understand excel files are not the best way to go for importing and exporting data but it is what is required in this particular case.
I might look for a way to just export to a flat text file and then try to convert to excel as a last step in the package. I'm hopping this wont trigger the same unicode / nonunicode conversion error.