March 5, 2014 at 8:28 am
I have a DTSX package that receives, daily, 5 flat files. It's a known format of extract so it's an easy build; however, one of the files is always emtpy. Another vendor had the same setup but suddently started providing data in the last file which we didn't know about several weeks. I'd like to test a table, or The Flat file itself, and if it suddenly has data, send an email, then, kill the package as it's going to cause all kinds of trouble until we can deal with it. At this point I am looking for ideas as we run into this scenario a bunch.
March 5, 2014 at 8:36 am
You can use a script task to find if the file is empty and then use precedence constraints to direct the control to a Send Mail task reporting package failure.
Or You can use a Fore Each Loop container looping through all input files using a script task and then direct your control flow accordingly ..
--
SQLBuddy
March 5, 2014 at 8:47 am
Unfortunately, I don't know how to write Visual anything these days... script task looks like a wash.
I could write a SQL Script to do it, then the only question is how to kill the package if it finds the data.
March 5, 2014 at 9:02 am
CptCrusty1 (3/5/2014)
Unfortunately, I don't know how to write Visual anything these days... script task looks like a wash.I could write a SQL Script to do it, then the only question is how to kill the package if it finds the data.
You can use Precedence Constraint Logic and direct the control flow so that the package doesn't execute. There is no direct way to kill the package from within.
Just use Flat file Validation as first step in the package.
Use 2 Precedence Constraints .. On Failure go to SEnd Mail Task
On Success go to Rest of the Package Execution.
This way the package will run only when the Validation check Passes.
You can even tie up Precedence Constraints with AND \ OR conditions too ..
Check this link for more on Precedence Constraints ..
--
SQLBuddy
March 5, 2014 at 9:26 am
Similar to what sqlbuddy said, if you use a For Each Loop container to process your files, you will be storing each filename in a variable. If you then use a Data Flow Task within the For Each Loop container to extract the contents of each flat file, you can include a RowCount transform as part of the Data Flow Task.
The RowCount transform requires that the row count value is written to a variable so if you check the value of that variable AND the value of the FileName you can then route accordingly using an Expression and Constraint. If a Send Mail task is at the end of one possible route through the Data Flow Task you can set the ForceExecutionResult property on the Send Mail task to Failure and the FailPackageOnFailure property to True.
Conceptually it might look something like the attached image.
Regards
Lempster
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply