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.
Best Answer
As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query.
Usage
In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.
Instead of
use a query such as
The astute students of AdventureWorks will recognize the data was already an
n(var)char
type but this was merely to demonstrate the concept.Benefits
Data Conversion Component
in SSIS