Type Conversion changes between Local PC and Server environment

Question

I am taking some simple data from an SQL table, making a small transformation and converting it to Unicode. Then I output it into an Oracle CHAR(1 byte) field on an Oracle server.

This works without any error on my local PC. I then deploy to the server and it says that

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

After trying several things I threw my hands up in the air and just took out the data conversion to unicode and now it is broken and wont run on my PC.

BUT - it now works on the server and is all happy. I've searched and found that others have had this problem, but none seem to find the cause and just work around it it other ways.

Why can I not have my PC and my Server work the same? All tables and data connection are the SAME for both. No change other than execution location.

Answer 1

I got the same issue in my SSIS package and found no solution.

It was a simple data, not containing any unicode character, and it doesn't throw any exception if converting it using an SQL query or a .net code ... But it throws an exception in SSIS when using Data Conversion Transformation

Workarounds

I made a simple workaround to achieve this (you can use it if this error occurs again)

1. I replaced the Data Conversion Component with a Script Component
2. I marked the columns i want to convert as input
3. For each column i want to convert i created an output column of type DT_WSTR

4. In the Script for each column i used the following code: (assuming the input is inColumn and the output is outColumn)

   If Not Row.inColumn_IsNull AndAlso _
      Not String.IsNullOrEmpty(Row.inColumn) Then
   
      Row.outColumn = Row.inColumn
   
   Else
   
      Row.outColumn_IsNull = True
   
   End If
   

OR

If the source is an OLEDB Source you can use a casting function in the OLEDB source command

ex:

SELECT CAST([COLUMN] as NVARCHAR(255)) AS [COLUMN]
FROM ....