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

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 2

Sometime we get this error when we select static character as a field in source query/view/procedure and the destination field data type in Unicode.

Below is the issue i faced: I used the script below at 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 multiple options but none worked for me. Then I prefixed the static value with N to make in Unicode as below:

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

Answer 3

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

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 work around 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 4

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.