How to fail Job only for certain SSIS Data Flow Tasks?
-
Ahoi!
Situation:
i have different DFT that import data from different SAP tables into my SQL Server tables.
The tasks are in a package that is called multiple times by parent package which has a loop to call the child package X times with different variables to execute different DFT in the child package .
Some of these DFT are less important and therefore would not be to crucial if they were to fail and therefore it would not be necessary to kill the whole Job.
Question:
How do i mark tasks i wish to make the Job Fail/Not to Fail if there is an ERROR while execution of the DFT?
So far without success i tried:
- setting FailPackageOnFailure and FailParentOnFailure
- in the indiviudual DFT
- in the child package itself
- in the paket task of the parent packet which is called multiple times to start the child package
- Setting the MaximumErrorCount to 0/1 in the DFT of the child DFT
https://stackoverflow.com/questions/435651/how-can-i-manually-fail-a-package-in-integration-services
- Adding a SQL Task to run: raiserror('Too few rows in my_table',16,1)
- Adding a Script Task with the change in the suggestion: Dts.TaskResult = (int)ScriptResults.Failure;
- This topic was modified 5 years, 5 months ago by ktflash.
- This topic was modified 5 years, 5 months ago by ktflash. Reason: Typos
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was - setting FailPackageOnFailure and FailParentOnFailure
-
How do i mark tasks i wish to make the Job Fail/Not to Fail if there is an ERROR while execution of the DFT?
The package will fail automatically if the Data Flow Task fails. If you want to not not propagate the error for the Data Flow Task, then you need to add an Event Handler.
Go to the Event Handlers Pane and then fine your Data Flow Task in the Executable Drop Down List and then select OnError in the Event handler drop down. In the middle of the screen there will be some text stating "Click here to create an 'OnError' Event hander for executable '{Your Task Name Here}'"; click it.
Then go to the variables pane a expose the system variables (Click the Grid options icon and then tick the Show system variables box). Find the system variable Propagate, and change the value to False.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
The package will fail automatically if the Data Flow Task fails. If you want to not not propagate the error for the Data Flow Task, then you need to add an Event Handler. Go to the Event Handlers Pane and then fine your Data Flow Task in the Executable Drop Down List and then select OnError in the Event handler drop down. In the middle of the screen there will be some text stating "Click here to create an 'OnError' Event hander for executable '{Your Task Name Here}'"; click it. Then go to the variables pane a expose the system variables (Click the Grid options icon and then tick the Show system variables box). Find the system variable Propagate, and change the value to False.
I am kinda confused.
I made a dataflowtask that tries to convert a string into a int as source into a random table just to produce a dataflow task that will run into an error. And added an error handler as described above?
The job still runs into an error and stops if i run it.
Ok maybe i missed to add the follwing, its a 3 stage SSIS package call.
Theres 1 package that calls the first child (first child = parent package I described in the original post).
This child now loops and calls the grandchild package which i described as child in the original post.
Is this the problem? --> Is there some kind of problem that the error communication is not passed properly throug these 3 layers?
So far the only thing i managed to achieve is making the job continue to run even if there is an error by setting MaximumErrorCount to 2 in the highest/first layer package. Problem is this only ignores 1 error no matter the DFT, which does not decide between necessary DFT or not necessary DFT.
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
Anyone any idea?
- i created an sql execution task with a wrong query to provoke an error which just says "SEL"
- Task name is "Fehler beabsichtig" and i added an empty Error Handler OnError with Propagate = False
- from what i was reading here and on other pages was that this should prevent the error from being passed further and the package will continue but i still get an error from the parent
- It still throws maximumerrorcount 3 (because the failed task is in a container that is executed three times)
- I also tried setting maxerrorcount to 10 within the lowest executing package, container and task, still the same
- I also tried setting an error handle to with propagate false to the lowest package itself, the task and the container
- Result is still this.... í am absolutely clueless what else i could to to prevent a task from making the job fail
EDIT:
The only solution that works so far to is to set the maximumerrorcount of the root execution package above 1.
This solution is kinda worthless though since it does not make a difference if its a necessary or not necessary task it continues for all of them.
ANY IDEAS?
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
Not perfect, but after alot trial and error i found the solution:
I seperated the necessary and not necessary tasks in the first package which is called.
The ones not necessary are within an container that has MaximumError > 1
The 2nd Level package which is called by the the now seperated package N times has a loop container which now also has a MaximumError > 1. Now he continues after a not necessary package runs into an error while the necessary still run into an error because the first package still has MaximumError = 1 for those.
It is not perfect, since depending on the reason for the error the SSIS throws a different amount of Errors.
My problem was that when i only had the MaximumERROR > 1 in the highest package the job would stop for this packet task and not continue. This was resolved by adding the MaximumERROR > into the 2nd level package which now continues to loop through instead of stopping there.
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply