October 27, 2011 at 4:23 pm
We have a ssis package.
In the package it will drop tables first, then load data and then add constraints like primary key or foreign keys.
The source is flat file, usually it is pretty clean and have the same constraints as the destination tables.
But it happened once, one of the source flat file doesn't have the same primary key as the destination.
And the pacakged failed because there is one duplicated record in the flat source file that destination table thinks it is primary key violation.
It then failed the whole package.
My question is is there a way in ssis to address this issue something like this:
When it has a primary key violation for that record, log it to a table, but still excute with the following rows. and didn;t fail this data flow and didn't fail the package either.
Thanks
October 27, 2011 at 5:07 pm
I assume the data loaded but the application of the constraints failed?
You can either apply the constraints before inserting the records and then divert errors to another destination (table if you like), otherwise, mimic the constraints within the dataflow and catch/divert the violations yourself.
Am I missing something?
HTH
October 27, 2011 at 5:16 pm
sam.dahl (10/27/2011)
I assume the data loaded but the application of the constraints failed?
-- Yes, the data loaded fine, but the next step in pacakge is to add constraints, and it failed, then it failed the whole package.
[You can either apply the constraints before inserting the records and then divert errors to another destination (table if you like)
-- If in this case, we only have one duplicated primary key records, will the pacakge still go on excuting adding constraints to other records or it stopped at that record?
[otherwise, mimic the constraints within the dataflow and catch/divert the violations yourself.
Not sure what this means?
Am I missing something?
HTH
October 27, 2011 at 5:27 pm
There's a 'redirection' choice on OLEDB Target.
Open the target, on the left, go to Error Output. Chose 'Redirect Row'.
Send that row off to a simple flatfile or something, or let it die in the Aether. I do that occassionally to avoid rare duplicated inserts, but want the option for later or to review a data-viewer myself when I load the file in BIDS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 27, 2011 at 5:28 pm
What I mean by the former suggetion is to change the logical structure of your package so that the tables are dropped, then the constraints are applied (application of constraints won't fail, because there is no data. but I guess you'd have to add a step to create the table first, or else change the drop to a truncate, but in that case if you still wanted to load without constraints you'd have to drop the constraints explicitly), then the data is inserted. Depending on the settings you are using the destination will quite happily insert all valid records, throwing away, or diverting, any errors to another destination. (I believe it's the Maximum Insert Commit Size that's of interest, see: http://agilebi.com/jwelch/2008/09/05/error-redirection-with-the-ole-db-destination/)
EDIT: See Craig's post above for the specific redirection steps.
Alternatively, my latter suggestion is to add some validation to your dataflow that checks for primary key violations. You could, for example, add a ROW_NUMBER() partitioned by PK_field to your query and a conditional split will happily divert any records with row numbers greater than 1 to your choice of destination. (I'm not sure if you can approximate a row numbering using the aggregation component, pk validation might require the use of a variable to store prior values and check for changes, I haven't thought the scenario all the way through, it will vary depending on your requirements)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply