Import Package Error - Cannot Convert between Unic

Question

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?

Answer 1

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.

Answer 2

Two solutions: 1- if the type of the target column is [nvarchar] it should be change to [varchar]

2- Add a "Derived 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 at the mapping part you should use this new added column instead of the original column.

Answer 3

This a solution that uses the IDE to fix:

1. Add a Data Conversion item to your dataflow as shown below;

2. Double click on the Data Conversion item, and set it as shown:

3. Now double click on the DB Destination item, Click on Mapping, and ensure that your input Column is actually the same as coming from the Copy of [your column name], which is in fact the Data Conversion output NOT the DB Source Output (be careful here). Here is a screenshot:

And thats it .. save and run ..

Answer 4

Resolved - to the original ask:

I've seen this before. Easiest way to fix (don't need all those data conversion steps as ALL of the meta data is available from the source connection):

Delete the OLE DB Source & OLE DB Destinations Make sure Delayed Validation is FALSE (you can set it to True later) Recreate the OLE DB Source with your query, etc. Verify in the Advanced Editor that all of the output data column types are correct Recreate your OLE DB Destination, map, create new table (or remap to existing) and you'll see that SSIS got all the data types correct (same as source).

So much easier that the stuff above.

Answer 5

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

Answer 6

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

1. Right-Click Ole DB Source
2. Select 'Edit'
3. Select Input and Output Properties Tab
4. Under "Inputs and Outputs", Expand "Ole DB Source Output" External Columns and Output Columns
5. In Output columns, select offending field, on the right-hand panel ensure Data Type Property matches that of the field in External Columns properties

Hope this was clear and easy to follow