December 16, 2010 at 11:17 pm
Hi!
Here's the problem, we have a DB2 source table with more than 200 columns some of which are char data type. Now the goal is to extract the data and put them into the destination table wherein the columns with char data types are now in nvarchar.
When I don't do any transformations, SSIS will fire an error Unicode/Nonunicode data type mismatch.
Does anyone here know of the best (and fastest) way to transform these 200 columns to nvarchar without having to do them in data conversion object 200 times over and manually mapped them?
thanks!
December 17, 2010 at 3:35 am
If possible, load the data into a staging table accepting the data from DB2. Then, do the conversion in your database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 20, 2010 at 12:16 am
just curious, by "do the conversion in the database" you mean using sql script? I am really not into databases but is there a way that I could do the conversion in DB2 query? meaning I have a select statement in DB2 that converts the char data types to nvarchar?
December 20, 2010 at 12:38 am
I have absolutely no experience with DB2, so I have no idea.
It should be possible I guess.
It ain't that hard to do the conversion in SSIS. Add a data conversion component. There you can select all columns using shift-click. Then check the checkbox for all columns. Edit the XML file of the package with search and replace to do your conversion.
Or, write all the data to a staging table in SQL Server and write a simple select statement with the cast/convert function to move the data to your final table. If you can write a SELECT statement for DB2, then you can write a SELECT statement for SQL Server 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply