When it comes to ETL, I’m a pessimist. Until proven otherwise, I assume that all data is bad, all connections are volatile, and all transformation logic is suspect. As such, I spent a lot of time addressing how to prepare for and handle errors in the ETL pipeline with SSIS. Building packages to expect and properly handle errors is a key component in a well-performing, durable, trustworthy ETL infrastructure.
SQL Server Integration Services has a number of built-in tools for handling errors and other anomalies. Among these:
- Event handlers, defined at the package, container, or task level
- Precedence constraints on the data flow to change the execution path in the event of an error
- Error outputs on sources and destinations in the data flow to redirect errors on a row-by-row basis
- Fail the task (and its ancestors), which is the default behavior
All of these options provide a way to handle or otherwise report the error. But what if your scenario requires that any error is simply ignored? SSIS can do that, too. In this post, I’ll show you how to use a particular attribute of event handlers to prevent the propagation of errors in SSIS executions.
Scenario
Let’s say that we have an SSIS package that processes a series of flat files using an instance of the ForEach Loop Container. The expected behavior is that we specify the directory of files to be processed, and the loop container will process the specified file(s) in that directory. If a file exists in that directory that fails to process – perhaps its security settings do not allow it to be read by the account executing the SSIS package, or the file is improperly formatted – the default behavior is that the loop container would fail, which would fail the package and any parent package that executes it. This is known as propagation (remember that word), which can be visualized as a “bubbling up” of errors. In that way, propagation of errors is not unlike the way exceptions will bubble up in C# or VB.NET programming.
Now in this same scenario, let’s assume that our business needs dictate that we don’t have to successfully process all of the files for the ETL load to be considered a success, and having a partial load of data has greater business value than failing the entire load if one file fails to load. In that case, we’ll want to override the default behavior of our package to allow certain elements to fail without affecting the outcome of the package.
As shown below, I’ve set up a package for our scenario in which we truncate the output staging table, and then loop through a specified directory to process each text file in that directory.
Notice that I’ve got precedence constraints set up after the data flow task in the loop container. This will allow me to log either a success or failure of a load operation on a file-by-file basis. The data flow task, which will be executed once per file in the source directory, will attempt to process each text file in that directory as shown below.
In this updated scenario, I don’t want to allow a failed file load to interrupt the load of the remainder of the files, so I’m going to make a couple of modifications. First of all, I’ll create an error event handler for the data flow task.
You’ll notice that there are no tasks in the error event handler for the data flow task (DFT Load File). Although you can add some custom logic here to execute upon error, you don’t necessarily need to do so. However, creating the error event handler will expose a particular setting we’ll need to prevent the propagation of any errors.
While the error event handler shown above is still in view, open the list of SSIS variables. Note that you’ll also have to set the variables window to show system variables, which are hidden by default. In the list of variables, scroll down until you find a variable named Propagate. This Boolean value is the setting that specifies whether errors within a given executable to bubble up to the ancestor tasks, containers, and packages. To prevent these errors from bubbling up, I’ll change the value of the Propagate variable from True to False.
The net result is that any error in this data flow task will still be shown as an error in this task, but that error will short circuit and will not be passed along to any executables further up the chain.
Final Thought
You’ll notice that in the control flow of this package, I included two different paths from the data flow task – one with the On Success constraint, and the other with the On Failure constraint. I’ve done this to allow the package to separately log the file-by-file results (success or failure, along with row count). The reason I’ve shown this is because I want to emphasize that, in most situations, you should be logging any error such as this – even if your package is built not to fail when an error is encountered. In my opinion, most any operation that it attempted, even if it doesn’t affect the outcome of the package, should be logged – especially in the event of failure.
Conclusion
In this post I’ve demonstrated how the built-in components in SSIS can be used to allow for the continued operation after the failure of one executable in a package. By modifying the Propagate system variable in the error event handler of a volatile executable, you can prevent the automatic failure of upstream package elements, allowing continued execution after the failure of noncritical operations.
Terrible idea. There always seem to be unintended side effects to setting propagate to false. I can’t imagine a scenario where it’s not better to just fail the package.
Ckuhn203,
What unintended side effects have you discovered?
The Propagate property should not be used to be used simply to avoid getting an error in SSIS. As I mentioned in the post, there has to be a business value in allowing certain operations to fail without disrupting the remaining operations in the same package (or group of packages). Further, like any proper error handling strategy, logging should be a part of any such design. As shown in the example, setting the Propagate property does not prevent the logging of such errors. The executable in question will still fail, but the error won’t bubble up to the ancestor executables.
Definitely not a terrible idea. I have a package that gathers data and sends emails in a loop. If an email address is bad, I don’t want that one error to cause the others not to be sent. Question Tim- is the propagate setting specific to the OnError event handler for the specific executable I configured the event handler for, or does it get used elsewhere that I might not be aware of?
Each executable (including containers and loops) will have its own Propagate setting. Because errors bubble up to their containers, a propagate setting would prevent any lower-level error from bubbling up any further.
You state “the default behavior is that the loop container would fail, which would fail the package and any parent package that executes it” but your solution does not stop a parent package from failing
Hi Ryan! You’re absolutely right. In further testing, I discovered that when using this pattern in a parent-child architecture, the parent package would still fail even though the child package succeeds. I’m planning to address this in a future blog post.
To use this method in a parent-child relationship where you want to suppress error propagation from the child, you would set the System::Propagate variable value to False for parent’s Execute Package Task in its OnError Event Handler. I find it’s easiest to navigate to this property through Package Explorer in the parent package.
If your child package is in a foreach loop (common in parent-child architecture) and you only want to suppress it for one of the child packages, you can use the Expression for the variable and make it dynamic by setting its value based on the child package name, e.g.
@[User::SOME_VARIABLE_USED_IN_FOREACH_LOOP]==”\\PACKAGE_NAME_TO_SUPPRESS ERROR” ? False: True
No changes are required to the child packages in this scenario. Props to my colleague Praveena Parpati for working this solution out in a pinch. She has a few articles out on SQL Server Central.
I certainly agree that these approaches should only be used in production when you know exactly what you are doing but, there are certainly valid scenarios for its use.
Bruce – agreed. It’s not a broad approach that can be applied to every potential scenario, but in some cases it’s very handy.
This method is doing exactly what I want. We are loading data into FACT tables in ‘batches”. If any table (or set of data) in a batch fails I want to back out all successfully loaded data in that ‘batch’. This method lets me control that.
It also lets me continue onto the next batch, accepting that the previous ‘batch’ of data didn’t load, but optimistically, the next batch may be okay.
Its a matter of designing what you want and setting the Propagate variable on the relevant containers or tasks to give the desired execution flow.
Thanks Tim.
Eric, thanks for the comment. Glad this tip helped you out!
Is there a setting to enable before using the Propagate option? I have been doing exactly as instructed from this blog but my package keeps failing even though I have an OnError event handler with Propagate set to False
Hi there,
There should be no other setting that you have to enable to make this work. If you set it at the package level, however, it won’t have any effect – the package will still fail. Be sure that you have the Propagate property set on the event handler for the task that you want to allow to fail.
Hi Tim,
I noticed it was tricky to establish which OnError handler I was interacting with on the Variables pane. This was because the scope simply showed OnError regardless of the container or task object I was configuring.
Do you know of a way to be able reveal additional info as I had to temporarily delete the other OnError handlers to be sure I was tweaking the correct one.
Thanks
Eric
Hi Eric – If you’re working in the event handler, there will be a dropdown list at the top left of the control flow that will show which event handler you’re currently working in.
Brilliant. What if you just set the DisableEventHandlers Property of the DFT?
Ashish, that property impacts whether or not the event handlers fire, but won’t prevent errors from failing the package.
lets say i have a flat file with 10000 record, after reading 6000 record my package fail for some reason. Now i want to start my package reading record form 6001 record onward not from 1st record. How do i achieve that?
Restarting a package in the middle of a data flow execution is possible, but it takes some extra design measures. You’d need to use a lookup (either in the data flow or a JOIN in the database engine) to reprocess the data, skipping rows that have already been processed. An easier design pattern would involve deleting rows loaded as part of a failed execution, and rerunning that data flow from the beginning.
Totally disagree with first comment, this is not a terrible idea, for me its quite the opposite. Perfect solution. I receive a number of files daily that need to be processed into a database. If one of the files had some corruption the whole package execution failed and the files after the corrupted one were not processed.
This solution now allows me to remove the offending file, create an error message, email me said error message and then move on to the next file.
Thank you so much for sharing.
Thanks for the feedback, Alan! I agree that this pattern is an effective and elegant way to handle volatile elements within an SSIS package.
Great solution – in some simple cases it can be just what is required. I have a loop container which polls four directories for CSV files and does a number of things with each one. I don’t necessarily get files in each directory on each day, so in the absence of a simple ‘ignore if file doesn’t exist’ setting this will do just the job and prevent the whole package from failing. Nice work!
Great article! I use this approach and it works when the file can not be opened for some reason but when an error like truncation occurs in one of the Data Flow Tasks, the Foreach still fails. I have create an event handler for each data flow task in the foreach loop and set all Propagate=FALSE. Any thoughts what could be wrong?
I have a continuous loop (kind of deamon) within which I am performing all my work via different task.I have set propagate property fro each of these task/loop within the main loop as false to keep my main loop infinite. However I do want to pick (say count) any error and break the main loop to fail the package thus the job.Any thoughts?
Vinay, you might have some luck using the method I describe in the post linked below. The example therein is actually used for skipping items, but you could adapt this pattern to check for the number of errors instead, and fail if a sufficient number of errors has been encountered.
Thanks a lot!!!!
You saved me!!
Great! Glad it helped.
What if I still want to process all the files and still fail package at the end? So that I can move good files to archive folder and current folder will only have bad files.
Hi Rshakya, this is still possible with the Propagate flag. You could use the failure precedence constraint to set an SSIS variable indicating failure, and then check that variable after all files have been processed. If any of the files had failed, you could then force the package to fail using a script task.
Test for the individual file error (think of it as a soft error and set a Boolean var to true, or smallint if you want to count them), and continue processing as recommended by Tim.
When control returns to the point the file handling loop was called, test the soft error var and invoke your desired code.
Once you take responsibility for trapping certain errors you need to define the logic flow as Required by your specs or biz requirements (eg. logging, decision trees to determine if you should shutdown or continue).
Awesome thank you!
Thanks Steve! Glad you found it useful.
Tim,
I have set the propagate flag to false but the package still stops after failure. I have a for each loop like your example and within it there is a data flow just like your example, but I query sql instead of a file (sales file above). The t-sql in the data flow changes with every iteration of the loop. So say on the 3rd iteration the sql is invalid (table not found in db) that fails the data flow but it also fails the for each loop container.
[Get RowCount [24]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E14 Description: “Invalid object name ‘dbo.table_name’.”.
[Get RowCount [24]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E14 Description: “Invalid object name ‘dbo.table_name’.”
how can I continue with the loop even though one iteration fails?
Excellent!…You saved my day!
Great! Glad it helped you.
Hi Tim,just a question that if I am loading huge amount of data my package fails on 100 th record in the source how can I start loading from 101st record from next execution
Ashish, there is no built-in way to restart a data flow in mid-stream. If you re-execute a data flow, it will automatically run the entire data flow.
Hi Tim. I just tried this while one of our 12 data source servers was down. Basically there is a seq container with 12 dfts in it. I set propagate in the event handlers for all 12 to false. And yet the failed connection brings the whole job down. Is it possible propagate needs to be set on the dft’s themselves also, not just the event handlers? The only good thing was that the failed dft’s event handler email was sent.
Thanks for this blog post, helped us naviagate a few peskey data flows.
Hi,
Thanks for this detailed article.Will that work in my bleow case please?
I have this one master package that contains three sequence containers. Each seq container has two Execute Package Task. This master package is executed through a sql server agent job daily.
If one of the container from this master package fails, i want it to continue to the next sequence container–>for this i can change the precedence constraint between the containers to completion.
But my question is , if i do that ,will the job complete as success or failure?
I want to know this because, If the job completes as a sucess i wont be getting any notification email as its set up on failure. In that case i will have follow your article to add a ‘on error’ event handler on sequence conatiner and have a sent email task on error.
But if the job completes as failure, we will get the email anyway and i can get the details from exectution report on server.
So can u help me with this suituation please?is my approach of setting ‘on error’ event handler a right way?if yes should i be setting the variable to false as from your article?or should i be using ‘on task failure’ event handler?cant understand the diff 🙂 or can the sql server agent job itself will handle this kind of suituation?
Thanks a lot for your inputs in advance
Jenni 🙂
Hi there
I’ve tried setting up the OnError Continue method you outline above all to no avail.
The setup I have is pretty much like the one you describe above, only difference being that I’m looking to load a daily batch of Excel Workbooks into a SQL table.
The package will work from start to finish without an issue if the Tabs in the workbooks are set correctly, i.e. 06 April 2020 and not 6 April 2020. If the latter is present then I get the following error messages:-
[Excel Main Data Source [11]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
[SSIS.Pipeline] Error: “Excel Main Data Source” failed validation and returned validation status “VS_ISBROKEN”.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Which is reasonable, given that the SQL code cannot find the Tab because the “0” is not present. (That’s just one of the errors that I can encounter).
So, if I’m loading 40 odd workbooks and one has an error like that, then I want the load to write out the workbook’s details to a failure table and then continue on to the next workbook and so on and write the loaded files to a success table.
What is happening is when an error such as the above is encountered, the workbook’s details do get written out to the failure table, but then the package terminates.
I set up the Event Handler on the Data Flow Task to OnError and set the Propagate Variable to False.
I’m not sure what else to do, so any help will be greatly appreciated.
Many thanks in advance,
NJC