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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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>
0
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?
0
Roberto Madro R.Programmer AnalystAuthor Commented:
jimhorn;

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.

Thx
0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roberto Madro R.Programmer AnalystAuthor Commented:
Discovered this solution by testing various scenarios.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.