April 26, 2010 at 2:19 pm
Hi,
Here is the problem:
Oracle source table with CLOB.
SQL 2008 destination table.
Using SSIS.
1. execute SQL package to truncate destination table on SQL 2008.
2. data flow task to read Oracle source table and load it into destination table.
Easy enough? Not so!
Source table is on Oracle and has CLOB. If I execute 1 and 2 in one flow, then mysteriously, the CLOB column in the destination table is mismatched. For example, in source, ID1 = CLOB 1, ID2 = CLOB2, etc. but in destination ID1 = CLOB3, ID2=CLOB5, The CLOB column is just shifted, even though the records are still there.
However, if in design mode, I truncate the destination, then stop debugging, then execute the data flow task, it loads just fine. CLOB is matched with its ID.
I have NO idea what the cause is. Please help!
May 7, 2010 at 10:05 am
More details on this:
source is Oracle with CLOB, destination is SQL 2008.
Using SSIS, the package first truncates the destination table using a SQL task. Then with a Data Flow Task, loads the source.
Here is the interesting part:
if the truncate and Data Flow are in the same package, then the source and destination tables don't match. All records are in there, row counts are good, all the other fields match, except the CLOB field. The CLOB field is the only field that is mismatched. So row 1 gets the CLOB that belongs to row 3 in the source, etc. I tried to find some pattern of the mismatch but there isn't an obvious one.
However, if I separate the truncate and Data Flow into two packages, and put them into a master package and execute them sequentially, everything loads and matches up.
I have no idea what is going on. Anyone, please help!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply