Exception handling in SSIS Package Dynamic Connection

Question

SSIS Scenario

I have a SQL variable of type object. It contains all the connection to different servers/databases. I want to connect to those databases one by one and run a query.

Expected Exception Handling

If the SSIS Connection manager(Dynamic connection manger) is unable to find the connection to the server (probably the server is down) I want to Skip that connection (Database/server) and log that into the table and move onto the next Connection (Database/Server). The SSIS package should not crash.

My Implementation

I have Successfully Configured the SSIS package to Use Connection Manager (Dynamic Connection Manager) and Foreach loop to loop through the SSIS variable of type object. but I am not able to skip the Connection if the server/database is not found. it generates error that the server/databsse not found / problem with the connection and the SSIS package fails.

my experience in SSIS is one week old

Any help will be appreciated.

Answer 1

How about setting --> Force execution Result property of the task to success

Answer 2

I was looking for the same fix too. Seems the normal OnError Eventhandling does not work for issues that arise when connecting to a source DB.

There is another workaround I wanted to mention. You can handle the error in the Data Flow Task (OnError Eventhandler, set the System-Variable "Propagate" in that Eventhandler to false). I think this is still required, but not sure. I use it also to log the exception.

Afterwards you can set MaximumErrorCount in ForEachLoop to "0" (which means unlimited). I'm not exactly sure why it works, but trying to find a way to handle the scenario you described, I found this.

==

Just as an interesting observation: For debugging purposes I added an OnError Eventhandler to the ForEachLoop and set a breakpoint there in a dummy script. It was never reached. Nonetheless the ForEachLoop failed all the time until I set MaximumErrorCount to 0.

Answer 3

I dont think its possible to continue the package execution once it encounters an error. You need to control this behavior through a SQL Server table (or any other table for that matter).

Once the package fails, you can set a flag in the table saying that the package failed. The next time the package runs, you can start from this point on and continue the execution. But automatically skipping the down server is kinda pulling a rabbit out of a hat.

Another way you can do this is to ping each server before hand in a separate package and store the ping results in a table. Only pick those records (servers) whose ping results were positive. Otherwise just skip the server.