June 14, 2010 at 9:41 am
Hello.
I have an SSIS package I wrote that imports the data from one DB2 table to one SQL Server 2005 table.
I'm having problems with a couple columns in DB2. Both DB2 Columns are data type "CHARACTER" with a size of 2.
The corresponding columns in SQL Server 2005 are both char(2).
The DB2 column is a 2 character code. Some are two space empty strings (" ") and that is fine.
When I run my SSIS package I get the error "[Data Conversion [16]] Error: Data conversion failed while converting column "XXX_XXX" (481) to column "Copy of XXX_XXX" (7883). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "
I have ruled out truncation because I tried setting the SQL Server 2005 column to a length of 255 and same error.
I did a little more digging and noticed a few of the records in DB2 had a value of ".." which seemed strange. So I asked one of the mainframe / COBOL guys and he said they were "High Values".
SO I tried to handle this using a Derived Column. I can get past it if I check if the value is NOT a particular code (Such as "XX", "YY, or "ZZ"), then insert NULL. That works fine.
HOWEVER I can't be sure this code will always be the ONLY one that will ever be used. So I'm not happy with that work around.
My big question is how do I test for this "High Value" coming rom DB2?:unsure:
I'm thinking something like [Field] == "High Value" ? " " : [Field]
I tried [Field] == ".." ? " " : [Field] but it still threw the same error.
I also found this page in my research: Cast (SSIS)
I'm thinking the section that is talking about multibyte character code pages is revelant but so far I'm not sure.
I have attached some screen shots for reference.
I appreciate any insight anyone can offer.
Thanks! - George
June 14, 2010 at 12:17 pm
OK I think I solved it!
Here is what I did and I showed it to my DBA and he said it looked good on paper. It hasn't gone through any formal review or anything but at least my SSIS package works and keeps the other CODES intact now 😀
I used this expression in my Derived Column transformation :
(DT_STR,2,1252)XXX_XXX == ".." ? " " : (DT_STR,2,1252)XXX_XXX
The only odd thing is the problem values came over in SQL Server as "??" but I'm going to have SSIS call a SP to clean those to "" easy enough.
:w00t:
June 15, 2010 at 7:09 am
Hi.
Thanks for the tip! I used the following expression :
CODEPOINT(column) > 255 ? (DT_STR,2,1252)"ZZ" : (DT_STR,2,1252)column
It still brought over the problem data as "??" even though I tried "ZZ" to actualy see the ones that changed. But I can still call the SP at the end to update the "??" to "". And I like the CODEPOINT(column) > 255 because that should catch more errant data then what I was using.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply