Data conversion issues in SSIS Package

I'm experiencing this error in building my SSIS package, the data source is an Oracle database, the source datatype is "varchar", the destination database is SQL Server, and the datatype there is "nvarchar", in the "Conversion" transformation I'm setting the datatype to "Unicode string [DT_WSTR]", but it's not working and I'm getting these type of errors.

"Error at Data Flow Task [SQL Server Destination [868]]: Columns "CUSTOMER_ID" and "CustNum" cannot convert between unicode and non-unicode string data types.

Your thoughts.
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
Roberto Madro R.Programmer AnalystAuthor Commented:
I figured it out and it worked, in the "Input and Output Properties" (on the Advanced Editor)  of the "Data Conversion" element in Data Flow, you have to change the Data Type of each "COPY" of the Output Columns in the "Common Property" area to "Unicode string [DT_WSTR]", doing that solved that issue for me.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's what you have to do dude..

(1)  Add a data conversion task in between your source and destination. Data Conversion Task in Data Flow(2)  Double-click to open, and add a row for each value where this is an issue.
       In the Output Alias column give it the same name but with a _String suffix
       In the Data Type column choose String [DT_STR].Data Converstion Task, add columns to convert(3)  In your destination, map the above _string column(s) to your destination.

<edited to add screen shots>
Roberto Madro R.Programmer AnalystAuthor Commented:
Let's make sure we're on the same page:
1- I've added a "Derived Column" process from the transformation
2- In the "Advanced Editor" under "Input Columnns" I added all the desired columns
3- In the "Input and Output Properties" under "Derived Column Input / Input Columns", I can see all the columns selected, selecting any of them however give access to only the name of the column in the "common Properties" window and nothing else (all grayed out).

Are we on the same page?
Roberto Madro R.Programmer AnalystAuthor Commented:

I'm getting the same error message as before, although I followed your instruction line by line. For some reason I believe string[DT_STR] maybe the wrong datatype here and I may need to be Unicode string[DT_WSTR], what do you think.

Roberto Madro R.Programmer AnalystAuthor Commented:
Discovered this solution by testing various scenarios.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.