December 5, 2012 at 9:55 am
What is the best way to make your SSIS Data Flow to fail upon a data condition check. What I'm doing is reading a flat file that has both detail records (counting this up) and a single trailer record (that has number of details records sent). If the two values do not match then fail. I'm at the point where I have the two values (total detail records, and trailer detail record count) to compare. I have a derived column ((DT_I8)DETAIL_REC_CNT == (DT_I8)[INT_SENT-REC-SNT] ? TRUE : FALSE
Upon the FALSE I want to Fail (stop the SSIS). I've only written a few SSIS's to date and have never dealt with ERROR path output. Is this how it's handled, and how?
Thank You
December 6, 2012 at 6:34 am
bobd125 (12/5/2012)
What is the best way to make your SSIS Data Flow to fail upon a data condition check. What I'm doing is reading a flat file that has both detail records (counting this up) and a single trailer record (that has number of details records sent). If the two values do not match then fail. I'm at the point where I have the two values (total detail records, and trailer detail record count) to compare. I have a derived column ((DT_I8)DETAIL_REC_CNT == (DT_I8)[INT_SENT-REC-SNT] ? TRUE : FALSEUpon the FALSE I want to Fail (stop the SSIS). I've only written a few SSIS's to date and have never dealt with ERROR path output. Is this how it's handled, and how?
It sounds to me like you want to use a Conditional Split transformation. If the counts match, then continue your processing other wise send to another branch where you can log the error (or do whatever else you may need). You wouldn't need the Derived column transformation (unless you need that True/False value somewhere farther down in your processing) using the conditional split.
http://www.bimonkey.com/2009/06/the-conditional-split-transformation/
HTH,
Rob
December 6, 2012 at 6:59 am
robert.gerald.taylor (12/6/2012)
bobd125 (12/5/2012)
What is the best way to make your SSIS Data Flow to fail upon a data condition check. What I'm doing is reading a flat file that has both detail records (counting this up) and a single trailer record (that has number of details records sent). If the two values do not match then fail. I'm at the point where I have the two values (total detail records, and trailer detail record count) to compare. I have a derived column ((DT_I8)DETAIL_REC_CNT == (DT_I8)[INT_SENT-REC-SNT] ? TRUE : FALSEUpon the FALSE I want to Fail (stop the SSIS). I've only written a few SSIS's to date and have never dealt with ERROR path output. Is this how it's handled, and how?
It sounds to me like you want to use a Conditional Split transformation. If the counts match, then continue your processing other wise send to another branch where you can log the error (or do whatever else you may need). You wouldn't need the Derived column transformation (unless you need that True/False value somewhere farther down in your processing) using the conditional split.
http://www.bimonkey.com/2009/06/the-conditional-split-transformation/
HTH,
Rob
What's ugly about all of this is that you have to read all of the rows & do a check before deciding whether to proceed.
A conditional split is part of your dataflow which (unless you have a clever solution I haven't thought of) would be checked for every input row & would therefore always fail - you don't reach the row containing the count until the very last row.
I'd be tempted to stage what's in the file (just the data rows, not the summary info), then compare the number of rows in this with what the trailer row says & then decide what to do next.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 6, 2012 at 10:32 am
So ignore the Derived Column and just used "((DT_I8)DETAIL_REC_CNT == (DT_I8)[INT_SENT-REC-SNT] ? TRUE : FALSE " is the Conditional Split. And how do I set the Error Output, or default path?. I would like to have the DataFlow stop with an error.
December 6, 2012 at 11:20 am
bobd125 (12/6/2012)
So ignore the Derived Column and just used "((DT_I8)DETAIL_REC_CNT == (DT_I8)[INT_SENT-REC-SNT] ? TRUE : FALSE " is the Conditional Split. And how do I set the Error Output, or default path?. I would like to have the DataFlow stop with an error.
As I alluded in my previous message, there is a fundamental problem with trying to do this all in a data flow. Data flow transformations occur for every row, as they pass through the pipeline.
How can you put a conditional split on row 1 of the data, when you do not yet know the total number of rows in the trailer?
Derived columns are similarly afflicted.
You need a way of reading all rows and doing the check before the rows go anywhere important. That is why I suggested an initial import to a staging table.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2012 at 9:50 am
Phil is right on target. You have to deal with the entire file before you can evaluate the number of rows. Staging seems like the only solution to me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply