Import Package Error - Cannot Convert between Unicode and Non Unicode String Data Type

Question

I am trying to troubleshoot this error without success.

I have made a dtsx package on my computer using SQL Server 2008. It imports data from a semicolon delimited csv file into a table where all of the field types are NVARCHAR MAX.

It works on my computer, but it needs to run on the clients server. Whenever they create the same package with the same csv file and destination table, they receive the error above.

We have gone through the creation of the package step by step, and everything seems OK. The mappings are all correct, but when they run the package in the last step, they receive this error. They are using SQL Server 2005.

Can anyone advise where to begin looking for this problem?

Thanks Mike Thomas

Answer 1

At some point, you're trying to convert an nvarchar column to a varchar column (or vice-versa).

Moreover, why is everything (supposedly) nvarchar(max)? That's a code smell if I ever saw one. Are you aware of how SQL Server stores those columns? They use pointers to where the column is stored from the actual rows, since they don't fit within the 8k pages.

Answer 2

Non-Unicode string data types:
Use STR for text file and VARCHAR for SQL Server columns.

Unicode string data types:
Use W_STR for text file and NVARCHAR for SQL Server columns.

The problem is that your data types do not match, so there could be a loss of data during the conversion.

Answer 3

The problem of converting from any non-unicode source to a unicode SQL Server table can be easily solved by:

• add a Data Conversion transformation step to your Data Flow
• open the Data Conversion and select Unicode for each data type that applies
• take note of the Output Alias of each applicable column (they are named Copy Of [original column name] by default)
• now, in the Destination step, click on Mappings
• change all of your input mappings to come from the aliased columns in the previous step (this is the step that is easily overlooked and will leave you wondering why you are still getting the same errors)

Answer 4

Mike, I had the same problem with SSIS in SQL Server 2005... Apparently, the DataFlowDestination object will always attempt to validate the data coming in, into Unicode. Go to that object, Advanced Editor, Component Properties pane, change the "ValidateExternalMetaData" property to False. Now, go to the Input and Output Properties pane, Destination Input, External Columns - set each column Data type and Length to match the database table it's going to. Now, when you close that editor, those column changes will be saved and not validated over, and it will work.