Column "" cannot convert between unicode and non-unicode string data types

Question

I am trying to import the data from the flat file into the Azure SQL database table and I have a merge to merge with another source too. But when I map the fields from the flat file to the Azure SQL database I keep getting the error like

Column "Location" cannot convert between unicode and non-unicode string data types

Upon looking at some forums I tried to change the data type of the field to Unicode string[DT_WSTR] and even I tried to have string [DT_STR]

The Destination Azure SQL database below is the Location field

Can anyone please suggest what I am missing here? Any help is greatly appreciated

Answer 1

Changing the columns data types from the component's advanced editor will not solve the problem. If the values imported contain some Unicode characters, you cannot convert them to non-Unicode strings, and you will receive the following exception. Before providing some solution, I highly recommend reading this article to learn more on data type conversion in SSIS:

• SSIS Data types: Change from the Advanced Editor vs. Data Conversion Transformations

Getting back to your issue, there are several solutions you could try:

1. Changing the destination column data type (if possible)
2. Using the Data conversion transformation component, implement an error handling logic where the values throwing exceptions are redirected to a staging table or manipulated before re-importing them to the destination table. You can refer to the following article: An overview of Error Handling in SSIS packages
3. From the flat file connection manager, got to the "Advanced Tab", and change the column data type to DT_STR.