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

Question

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

It works on my computer, but it should run on the client server. Whenever they create the same package with the same CSV file and destination table, they get the error above.

We created the package step by step, and everything seems to be OK. All mappings are correct, but when they run the package in the last step, they get this error. They use SQL Server 2005.

Can anyone advise where to start this problem?

Answer 1

The problem of converting from any non-Unicode source to a SQL Server Unicode table can be resolved with:

• add step to convert data to data stream
• open Data Conversion and select Unicode for each data type used.
• note the output alias of each applicable column (by default they are called Copy [source column name])
• Now in the "Destination" step, click "Match"
• change all of your input mappings coming from columns with an alias in the previous step (this is a step that is easy to overlook and will leave you wondering why you get the same errors anyway).

Answer 2

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

Moreover, why is everything (presumably) nvarchar(max) ? This is the smell of code if I have ever seen it. Do you know how SQL Server stores these columns? They use pointers where the column is stored from actual rows, since they do not fit within 8k pages.

Answer 3

Non-Unicode String Data Types:
Use STR for text file and VARCHAR for SQL Server columns.

Unicode String Types:
Use W_STR for the text file and NVARCHAR columns for SQL Server.

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

Answer 4

two solutions: 1- if the type of the target column is [nvarchar], it should be changed to [varchar]

2- Add the “Drived Column” component to the SSIS package and add a new column with the following expression:
(DT_WSTR, "length") [ColumnName]

length is the length of the column in the target table, and ColumnName is the name of the column in the target table. finally, in the display part, you should use this newly added column instead of the original column.

Answer 5

Not sure if this is the best practice with SSIS, but sometimes I find that their tools are a bit awkward when you want to do this type of activity.

Instead of using your components, you can convert data to your query

Instead of doing

 SELECT myField = myNvarchar20Field FROM myTable 

You could do

 SELECT myField = CONVERT(VARCHAR(20),myNvarchar20Field) FROM myTable 

Answer 6

Mike, I had the same problem with SSIS in SQL Server 2005 ... Apparently, the DataFlowDestination object will always try to check the data coming in to Unicode. Go to this object, in the "Advanced Editor", "Component Properties" window, change the "ValidateExternalMetaData" property to False. Now go to the "Input and Output Properties", "Login for Input", "External Columns" panel - set each column "Data Type" and "Length" according to the database table. Now, when you close this editor, these column changes will be saved and not checked, and it will work.

Answer 7

Follow the instructions below to avoid (unable to convert string types to unicode and non-Unicode). This error

i) Add a data conversion conversion tool to your DataFlow.
ii) To open DataFlow Conversion and select the data type [string DT_STR].
iii) Then go to the Destination stream, select Display.
iv) change your i / p name to a copy of the name.

Answer 8

This is the solution using the IDE to fix:

• Add a Data Conversion element to the data stream as shown below;

2. Double-click the Data Conversion element and set it as shown:

3. Now double-click the DB Destination element, click Mapping and make sure your input column is actually the same as copying [column name], which is actually Data Conversion NOT the DB Source Output (be careful). Here is a screenshot:

And here it is .. save and run ..

Answer 9

Get the client configuration in the registry and change LANG. For Oracle, go to HLM \ SOFTWARE \ ORACLE \ KEY_ORACLIENT ... HOME \ NLS_LANG and switch to the appropriate language.

Answer 10

The dts data conversion task is timing if there are 50 plus columns! Found a fix for this at the link below

 http://rdc.codeplex.com/releases/view/48420 

However, it does not seem to work for versions above 2008. So this is how I had to overcome the problem.

 *Open the .DTSX file on Notepad++. Choose language as XML *Goto the <DTS:FlatFileColumns> tag. Select all items within this tag *Find the string **DTS:DataType="129"** replace with **DTS:DataType="130"** *Save the .DTSX file. *Open the project again on Visual Studio BIDS *Double Click on the Source Task . You would get the message the metadata of the following output columns does not match the metadata of the external columns with which the output columns are associated: ... Do you want to replace the metadata of the output columns with the metadata of the external columns? *Now Click Yes. We are done ! 

Answer 11

Solved - ask the original:

I have seen this before. The easiest way to fix it (all these data conversion steps are not needed, since ALL metadata is available from the original connection):

Delete OLE DB Source and OLE DB assignment Make sure that Delayed Validation is FALSE (you can set it to True later) Restore the OLE DB source with your query, etc. Verify in the advanced editor that all types of output data columns are correct. Restore your OLE DB Destination, draw a map, create a new table (or change it to an existing one), and you will see that SSIS returned all data types (the same as the original ones) .

Much easier than the above.

Answer 12

Not sure if this is still a problem, but I found this simple solution:

• Right Ole DB DB Source
• Select "Change."
• Select the "Login and Logout Properties" tab
• In the "Inputs and outputs" section, expand "Ole DB source output" External columns and output columns
• In the Output columns, select the Violation field in the right pane, make sure the Data Type property matches the field field in the External Columns properties

Hope it was clear and easy to follow.

Answer 13

Sometimes we get this error when we select a static character as a field in the original query / view / procedure and the data type of the target field in Unicode.

Below is the problem: I used the script below in the source

and got the error message Column "CATEGORY" cannot convert between Unicode and non-Unicode string data types. as below: error message

Resolution: I tried several options, but no one worked for me. Then I prefix the static value with N to do in Unicode, as shown below:

 SELECT N'STUDENT DETAIL' CATEGORY, NAME, DATEOFBIRTH FROM STUDENTS UNION SELECT N'FACULTY DETAIL' CATEGORY, NAME, DATEOFBIRTH FROM FACULTY 

Answer 14

If someone is still experiencing this problem, I have discovered that this is due to the different versions of Oracle Client.

I have posted my full experience and solution here: https://stackoverflow.com/a/464632/

Answer 15

 1.add a Data Conversion tool from toolbox 2.Open it,It shows all coloumns from excel ,convert it to desire output. take note of the Output Alias of each applicable column (they are named Copy Of [original column name] by default) 3.now, in the Destination step, click on Mappings 

Answer 16

I changed ValidateExternalMetadata = False for each conversion task. It worked for me.