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

Question

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)

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.

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.

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.

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