February 15, 2010 at 1:25 pm
I have a parent package that loops through a collection of files & calls the appropriate child package(out of 4 child packages). If the file has bad data, I have a Boolean variable 'badFile' that gets set to 'True'.
HERE'S THE QUESTION: How can I abort/exit the child package as soon as the 'badFile' variable is set to 'True' without aborting the parent package also? As soon as 1 error is found, I need to exit the child package and move the file to an error directory with the file system task SO that the parent loop won't be interrupted.
I looked around and I see that there's a method 'RunningPackage.Stop()', but I need a way to specify WHICH package to stop to ensure that ONLY the child stops and not the parent as I need the parent to continue looping through the files and to move on.
I need help with this asap, so please respond- even if you have an alternative approach to take care of this problem
thank you!
February 16, 2010 at 5:36 am
Ragie,
I've had a similar issue not so long ago.
I was also looking for a clean way to 'exit the loop' if an error was found (similar to the continue statement in a foreach loop in C#).
I've found a way to achieve what you want, but it is not so clean:
(I assume that you read some files with a foreach loop, check the data and write the output to a destination table.)
1. Create a temp table.
For example:
SELECT TOP 0 * INTO ##Temp_MyTable FROM MyTable
2. In your DFT (which is run in a foreach loop, so it is called for each file), add a derived column component that adds two columns:
* the name of the file
* a status code that indicates if the row is erroroneous or not
3. After the foreach loop, inspect the temp table. If a certain file has an error-row, delete all the rows corresponding with that file with a SQL statement. (or just the row, if you want to import the rest of the file)
4. Write the temp table to the destination table.
It is a bit of a work-around, but it should work. If you have more questions implementing this solution, let me know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 25, 2010 at 4:44 am
ragie (2/15/2010)
HERE'S THE QUESTION: How can I abort/exit the child package as soon as the 'badFile' variable is set to 'True' without aborting the parent package also? As soon as 1 error is found, I need to exit the child package and move the file to an error directory with the file system task SO that the parent loop won't be interrupted.
Have you tried setting the MaximumErrorCount property to a higher value? This property exists at different levels (starting at the task host container over all containers higher in the hierarchy all the way up to the package level). You won't have to change anything in the child package. In your parent package raise it for all containers that you do not want to fail (Execute Package Task, Foreach Loop, Package etc.).
e.g. If you raise it only on the Foreach Loop, the loop will continue regardless of whether the child package or execute package task fails. But if you don't raise it on the package level (of the parent package), the latter will still fail so you'll know something went wrong.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply