I have an SSIS package with a data flow task that uses a script component as its data source. The script component retrieves its data from a web service. The call to the web service sometimes fails, so I want to be able to retry a few times before failing the package.
My situation is very similar to the one described here. I followed the steps outlined there, but my For Loop still fails even though the Propagate variable on the Data Flow Task is set to False.
My question is: are there any situations you are aware of that would cause an error to propagate to a parent container even when the Propagate variable says not to?
Some more details about the error: it is caused by the web service call timing out. (There is some problem with our connection to the web service that we have not yet been able to diagnose. The web service provider shows that they receive the request and respond, but our SSIS never gets the response back, and instead waits until it times out.) This raises a System.TimeoutException in the Script, which in turn causes an SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
Best Answer
I suggest you approach this issue differently. If you suppress errors, you lose insight into the myriad errors other than a connection time-out that can occur. Nothing worse than a silent error that causes you to lose data or load bad data for... how long until anyone will ever find this with no alerts and no SSIS failures?
Handle the error in the code itself. Use TRY CATCH blocks to execute your code to an expected stopping point. In your case you want to specifically handle a
System.TimeoutExceptionby trying to connect again. For any other exception the package should fail. By handling the issue in the code you can allow true failures to bubble up through the SSIS failure propagation, while addressing the specific errors you expect or have encountered gracefully in the script component itself.