April 7, 2015 at 10:25 pm
I'm working on an SSIS pkg. that reads records from a spreadsheet, does ETL on those records, and outputs altered info. into a new Excel spreadsheet.
The new Excel spreadsheet is then utilized by another third-party process. This third party process can't do anything with the records and will fail if any of the records have a "Base Comp" > 0 and a "Hours" > 0 and an IsNull(Date of Birth). It needs to be an all or none situation. For example, if there are 500 records in my original spreadsheet and it's determined that even 1 record has criteria meeting what I outlined above, then the SSIS pkg. needs to fail and I'll have an email sent that explains that one or more records in the originating spreadsheet was found to meet the "Base Comp" > 0 and a "Hours" > 0 and an IsNull(Date of Birth) criteria.
So basically, from the get-go, I want to assess the records in the original spreadsheet and if any meet my filtering criteria shown above, I want to fail the package and send my informative email, etc. If the filtering criteria above ISN'T met, then I want the rest of my transitions in the process to run within the package and keep on rolling.
I'm trying to figure out how to accomplish this... what type of transformation item do I need to implement and use in order to determine early-on in the SSIS pkg. that the execution of the pkg. can move forward or that none of the remainder of the items in the pkg. need to continue and the package execution needs to fail?
Seems simple, but I'm having trouble determining from my research this eve. exactly how to do / accomplish this.
Many thanks in advance for any assistance / points in the right direction.
April 8, 2015 at 1:14 am
You can either dump the data into a staging table and then check the validity of the data.
Or you implement a blocking script transformation where you load all records, check them and then output them if everything is fine.
The first option is the easiest.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply