SSIS – Stopping an Error from Propagating to its Parent (Enabling an SSIS Package to continue when an error happens)
Due to our environment and packages we create, we have to enable the packages to continue even if it fails for some reason (Cannot connect to a Server for example) so that the rest of the data will be captured.
In the past we configured the MaximumError count to be 100, but this is not ideal, because if another component fails which is critical, due to the configuration, the package would complete with success.
NOTE: The above is all configured in SQL Server Data Tools in SQL Server 2012.
1. In our example below, we are going to configure our Data Flow Task called: Extract Data From Live System for Purchases
a. We are going to configure the above data flow task, so that if an error occurs it will continue with the next system.
2. The first thing that you would need to do is to change the variables so that you can see the system variables by doing the following:
a. Go into your Variables, either by going to an existing window or by clicking in a blank area in your Control Flow, and clicking on SSIS, and then click on Variables
b. Now in your Variables window you will see a button which says Grid Options:
ii. Click on the Grid Options
iii. Now in the Grid Options Window under Filter, click on the Show System variables check box as shown below
2. Then click Ok.
iv. Now you will see all the System Variables as well as any other Variables you have already configured.
3. Next click on the Event Handlers Tab within your Package.
a. Where it says Executable, click on the drop down and select the Executable where you want to allow it to continue if it fails.
i. In our example the executable was called:
1. Extract Data From Live System for Purchases
b. This is what you will see once you have clicked on your Executable.
d. Ensure that the Event Handler is set to OnError
e. As shown above, where it says: Click here to create an ‘OnError’ event handler for executable ‘Extract Data From Live System for Purchases’
i. Click on it, so that once completed the above text will disappear and the Event Handler screen will be blank.
4. Now click on Variables Window, and scroll down until you see a System Variable called Propagate:
b. As you can see by default for the OnError it is set to True, which means if there is an error it will propagate to the error.
c. Click on the drop down next to True and change this to False.
d. NOTE: Now your package will not propagate the error for your executable
5. Now save your package and then run your package.
6. You can see from the image below, how the package continued after an Error