Converting Non Unicode string to Unicode string SSIS

Question

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.

• Patrick Honorez: how is your carpal tunnel syndrom after that ? :-)
• JustBeingHelpful: Make sure the table you move data to is using Unicode data types (ie: nvarchar(50) instead of varchar(50))

Answer 1

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

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

use a query such as

SELECT
 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
FROM
 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.

Benefits

• 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...

• Thronk: "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. :-)
• Patrick Honorez: speaking about RSI, CONVERT should be easier to paste 50 times than CAST, since the arguments are in front

Answer 2

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.

Answer 3

If you only have to change from "Unicode string (DT_WSTR)" to "string (DT_STR)" or vice versa, then you might do this:

1. Save a copy of your package.dtsx- file ( in the case you must recover )
2. Open the package.dtsx in a editor, or in MS Visual Studio right-click on the package and choose "View Code". You will see a XML-File.

Search for the string DTS:DataType if it is followed by ="130" , then the Column is defined as DT_WSTR (unicode).

If it is followed by ="129" , then the Column is defined as DT_STR (non-unicode), use search and replace with care (you made a copy before, didn't you?)

Save the file and perhaps this was it.

• peterh: Welcome on the DBA SE! Pro tip: the site has various text formatting features to make your posts better looking. Use the icons on the top of the editor textbox.