Ways to resolve Unicode and non-unicode data type issue in SSIS?

One of the common problem that we came across into SSIS is conversion of Unicode and non-unicode data type. This happens, when we are passing data from a unicode source to non-unicode destination or vice versa.

If the soruce and destination happens to be a DBMS or RDMBS system like SQL Server, Oracle etc. there are built-in mechanism or conversion functions that can be used to convert data from one format to another. For exampel with SQL Server you can use CAST or CONVERT function to convert your data before passing it to a destination.

If you are dealing with Ms-Excel source, you have two choices available into SSIS that you can use. Let us consider a typical scenario when we have a MS-Excel Source and a SQL Server table as Destination.
If you right click on the OLE Destination and select "Show Advanced Editor" you have the option of changing the DataType from string [DT_STR] to Unicode string [DT_WSTR].  But once you click on OK it looks like the changed was saved, but if you open the editor again the change is gone and back to the original value.  This makes sense since you can not change the data type in the actual table

When we connect the Excel Source to OLE DB Destination into SSIS design, we get the error that cannot convert between unicode and non-unicode string data type.

To resolve this issues we have following two solutions available into SSIS>

Derived Column Solution :

We can add a Derived Column Component between Excel Source and OLE DB Destination.

Inside the Derived column component, we can add a new column (strProductName) and use Type Cast operator DT_STR, which converts the Unicode data column to string data column.

Inside the OLE DB Destination we can map the Derived Column (strProductName) with the table column.

Data Conversion Solution:

We can add a Data Conversion Component between Excel Source and OLE DB Destination.

Inside the Data Conversion component, we can convert the columns data type from Unicode to string.

Inside the OLE DB Destination we can map the Output Alias Column (strProductName) with the table column.

There is a third solution for this problem but this solution is applicable if your source is a Flat file with Unicode data.

Flat File Solution:

You can right click on Flat File Source and go to Show Advanced Editor. You can go to the Input and Output Properties tab. You need to select OutPut Columns and change the data type to string.

This way the output columns is passed to the OLE DB Destination as string and running the SSIS package will complete successfully.