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

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

User · Answer

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

User · Answer

If anyone is still experiencing this issue  I found that it related to a difference in Oracle Client versions   I have posted my full experience and solution here  https   stackoverflow com a 43806765 923177

User · Answer

Not sure if this is a best practice with SSIS but sometimes I find their tools are a bit clunky when you want to do this type of activity   Instead of using their components you can convert the data within your query  Instead of doing  SELECT myField   myNvarchar20Field FROM myTable   You could do  SELECT myField   CONVERT VARCHAR 20  myNvarchar20Field  FROM myTable

User · Answer

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

User · Answer

I changed ValidateExternalMetadata False for each transformation task  It worked for me

User · Answer

Not sure if this is still a problem but I found this simple solution    Right-Click Ole DB Source Select  Edit  Select Input and Output Properties Tab Under  Inputs and Outputs   Expand  Ole DB Source Output  External Columns              and Output Columns 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

User · Answer

The problem of converting from any non-unicode source to a unicode SQL Server table can be 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

User · Answer

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

User · Answer

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

User · Answer

Follow the below steps to avoid  cannot convert between unicode and non-unicode string data types  this error  i  Add the Data conversion Transformation tool to your DataFlow  ii  To open the DataFlow Conversion and select  string DT STR  datatype  iii  Then go to Destination flow  select Mapping  iv  change your i p name to copy of the name

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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  lt DTS FlatFileColumns gt  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

User · Answer

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

User · Answer

This a solution that uses the IDE to fix     Add a Data Conversion item to your dataflow as shown below        Double click on the Data Conversion item  and set it as shown        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

[sql-server-2005] Import Package Error - Cannot Convert between Unicode and Non Unicode String Data Type

Examples related to sql-server-2005

Examples related to unicode

Examples related to ssis

Examples related to types

Examples related to non-unicode