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

sql-server-2005 unicode ssis types non-unicode
Mike Thomas picture Mike Thomas · Aug 19, 2009 · Viewed 246k times · Source

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

vanlandingham picture vanlandingham · Dec 15, 2009

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)

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More