January 20, 2011 at 6:57 am
Hi to all,
we've got a legacy DB2, and we've developed many ETL flows with SSIS, in order to migrate to SQL Server 2005. We've found an odd problem: there is one row in a table, where a key is written like this: VF10-0004[, (yes, there is a "[" at the end :angry:), but when we look-up in the SQL Server table, the value is written WITHOUT the [.
I've already tried with the autotranslate=no in the connection string of the ODBC connection to db2...but to no avail.
Any idea?
Thank you
Daniele
January 20, 2011 at 7:01 am
id the data truncated at the source or the destination?
to test this put a data-watcher on the dataflow directly after the source, and check if the '[' is still there
January 20, 2011 at 7:05 am
steveb. (1/20/2011)
id the data truncated at the source or the destination?to test this put a data-watcher on the dataflow directly after the source, and check if the '[' is still there
Hi steveb,
it's truncated in the source (I already don't see the '[' in the dataviewer).
January 21, 2011 at 8:11 am
Hi to,
I've solved it!
Since I'm using the DBReader with a query, I've modified the query, for that column, in this way:
cast(replace(cast(COL as varchar(20)),'°','[') as varchar(10))
The original field is a varchar(10). I suspected that the character was truncated due to some coding problems between DB2 and SSIS, and somehow SSIS saw this '[' char value bigger than 1 char, and truncated it. At first I just made a cast, and saw that SSIS read the '[' value as '°'. So I had to modify the DBReader in this way:
- CAST in order to make it bigger
- REPLACE in order to set the correct char
- re-CAST in order to have the right dimension.
The last 2 steps weren't sufficient unfortunately (I believe that the replace received a buffer of 10 characters, so it was already truncated).
The only PITA is to change all the SSIS using this FK, but at least, we get everything 🙂
January 21, 2011 at 8:24 am
Thats good news, thanks for reporting back the solution for others..
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply