Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 11 down vote accepted

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.


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

,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
    HumanResources.Employee AS E

use a query such as

    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.


  • Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
  • No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
  • No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...
share|improve this answer
"As an alternative to RDC" - What is RDC? When I read on to the next answer, I think you might mean Replacing Data Component, but as the author of the original post didn't mention that component, might be too early for an acronym. :-) – Thronk Sep 13 '13 at 17:28

Natively, there is no way to do that. But you can download - Replacing Data Conversion Component for SSIS from Codeplex and do that in one shot.

More info can be found here.

share|improve this answer
Awesome. Thanks. Definitely a time saver! – PokerPlayer23 May 6 '13 at 17:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.