June 3, 2010 at 3:26 pm
I am trying to load data from a staging table into the target table. The target has some constraints defined on it (such as PK, some columns aren’t null, etc). What I would like to do is :
•load all rows that don’t fail the constraints options into the target table
•load the rest of the data into error table.
I tried to implement it by defining a data flow task, with staging table as source, target table as target, set “error output” to “redirect row” (on the target) and add another target for error rows (after the actual target), pointing to the error table.
If there is a violation of constraints on the target, even on a single row, all rows go into Error.
That is not the behavior I’m looking for. I am looking to move only error rows to the error table, and the rest - to the target.
Please point me in the right direction – maybe I’m not using the right task? If I have to add transformations to check manually for all possible errors before loading, I can do the same in T-SQL..
June 4, 2010 at 7:59 am
I was looking for a way to rely on constraints defined on the target table to reject rows. Not auditing data before the load.
June 7, 2010 at 8:32 am
Thank you very much for reply. That explains it. It seems like the only option I have is doing pre-audits (something like a lookup transformation in SSIS or joins in t-sql). Am I correct?
June 9, 2010 at 12:32 pm
Thank you very much for your help.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply