November 20, 2007 at 7:51 am
Hello,
I have an AS400 source where the connection manager being used is the .NET provider for ODBC. The destination is a SQL Server 2005 table. Some of the source data contains some kind of character at the end of the string that I have not been able to remove. When I try to import the data into the SQL Server table, the package fails on a truncation error because of these extra characters.
When I take a sample of the offending data from my error output, I can see that the string causing the failure does not exceede the width of the destination column.
I've tried to exprement with different code pages (65001 and 37), but nothing has worked. I've tried using TRIM and RTRIM in the Derived Column transform, but that has not worked.
Any ideas?
Thank you for your help!
CSDunn
November 20, 2007 at 10:17 am
Do you know what is code of this character? Try to use Script Component and trace all chars for this column under debugger and next use character conversion component.
November 20, 2007 at 11:59 am
A Sample of errant data looks like this:
901 Hwy 282 SW?
You should see a box after 'W'. More 'space' characters follow that.
November 20, 2007 at 1:29 pm
CS,
Did you try using the REPLACE function in the derived column? If you can see the "box" you should be able to copy and paste it in the REPLACE.
Did you use a DSN-less connection? Create a system dsn and turn on the Translate Binary Data on the Translation tab of the DSN. I am assuming that you have iSeries Access for WIndows.
Regards,
Norman
DTS Package Search
November 20, 2007 at 1:42 pm
I never thought to just copy and paste the 'box' into a replace function. That worked. I set the 'convert binary data' in the DSN first, but that had no affect. I'll look at using an OLE DB provider.
Thank you for your help!
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply