January 10, 2012 at 9:44 am
I see how to fail the lookup transformation if matches are NOT found, but is it possible to also fail the lookup transformation if matches ARE found? I have some data that consists of adds, updates, and delete. I use a conditional split to filter out the adds. If the ADD has the same unique identifier that is already in database I don't want to import a duplicate. This should never happen, but I also don't want to assume it never will...
Thanks in advance for any suggestions you can give me!
January 10, 2012 at 9:49 am
Triality (1/10/2012)
I see how to fail the lookup transformation if matches are NOT found, but is it possible to also fail the lookup transformation if matches ARE found? I have some data that consists of adds, updates, and delete. I use a conditional split to filter out the adds. If the ADD has the same unique identifier that is already in database I don't want to import a duplicate. This should never happen, but I also don't want to assume it never will...Thanks in advance for any suggestions you can give me!
Why not just take the Record Not found branch from the Lookup and work with that?
For the record matches that you don't want, you could create an error logging table and just send the lookup successes to that, and then do your own custom handling if there are records in that table.
January 10, 2012 at 9:54 am
I could do that, but I am loading about 30 different files on this package all with different layouts. I would have to create 30 additional "error" tables and I think it would just get messy. I would rather just have the package fail so I could investigate the issue. In theory this will never happen.
January 10, 2012 at 9:56 am
Triality (1/10/2012)
I could do that, but I am loading about 30 different files on this package all with different layouts. I would have to create 30 additional "error" tables and I think it would just get messy. I would rather just have the package fail so I could investigate the issue. In theory this will never happen.
Could you split it to a script destination task that raises and error and triggers what you want?
January 10, 2012 at 10:08 am
That is a good idea, but how do I make sure the error is raised before any of the no match output rows are added to the database?
January 10, 2012 at 12:09 pm
I have a better idea, why don't you add a script component that takes the rows and builds an XML fragment which can be written into a common table, then using the XML datatype you could extract out each different kind of row..
CEWII
January 10, 2012 at 12:13 pm
Triality (1/10/2012)
That is a good idea, but how do I make sure the error is raised before any of the no match output rows are added to the database?
Make the Data Transform operate in Transaction Enforced mode and it will roll back when the error pops up.
Or for that matter, just turn on Transaction Enforced for the Data Transform and let the PK violation cause the roll-back
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply