SSIS Cannot Convert between Unicode and Non-Unicode Data Types

SQL Server Administration
1

This has been a common error for me in the past but but I'm not able to resolve it in this package.
My source is a flat file (.txt) and the destination is a SQL table with all columns defined as nvarchar.

In the Flat File Source - I've set all columns to DT_WSTR and mapped.

image
Notice the Code Page is 0 - I'm not sure why it isn't 1252 or 65001?

All columns in the table are defined as nvarchar.

The OLE DB Destination gives this error:

Error at Data Flow Task [OLE DB Destination [726]]: Columns "Column 0" and "ShipToAcct" cannot convert between unicode and non-unicode string data types.

Thanks for any ideas.

2

Is the file actually unicode? Or is it stored as ASCII?

Is the file formatted with a non-Windows row delimiter?

1 Like
3

Thanks, @robert_volk . I'll look into your questions. But I can say that I'm able to import the file into a NEW table using the Import Flat File Wizard.

4

@robert_volk The encoding format shows to be UTF-8-BOM. I'm not familiar with the BOM variation.

Notepad ++ shows the end of line characters to be CR|LF

5

@robert_volk I have got past this error. When I remember which of the 10 things I tried actually resolved it, I'll post back. Thanks