12

I want to implement something like the following: enter image description here

In this package, I want to loop through a list of database names, dynamically modifying connection manager settings.

However, the first failure on "Test Connection" will halt and fail the package execution.

But I'd like to continue my Foreach loop. I get the sense that this should be possible (otherwise what's the use case for these red failure arrows?)

Things I've tried include messing with "MaximumErrorCount".

12

To answer the question as I asked it, it is possible to continue after failures by making sure that MaximumErrorCount is zero for the task and its parent containers... In this case that means the "Test Connection" task, the "foreach" container and the package itself.

I ended up doing something different though. And Jamie Thomson's Verify A Connection Before Using It was helpful here. Here's what I did:

I replaced the Test Connection task with a script task. The script task was almost exactly like Jamie's script except that I

  • only used one connection manager, not all of them,
  • the task result was always success
  • and I set a new variable "User::ConnectionSucceeded" to the result of the connection test.

I also changed the arrows coming out of the test connection task to evaluate expressions, they evaluate:

  • @[User::ConnectionSucceed] == True
  • @[User::ConnectionSucceed] == False

respectively.

I also had to remember to edit the multiple constraints going into the "Update LastMonitored" task. I changed them to use a logical "OR".

Here's what my SSIS package looks like now: new package

1

The easy way...

Double Click on the Precedence Constraint (The green line) after the "Test Connection" task. You should see "Constraint options", click on the drop down list "Value" and select "Completion". This tells your package to continue running after the task is completed regardless if it fails.

Note: Do not change the drop down list "Evaluation operation" from "Constraint" unless you want to add an Expression to be evaluated after the task has completed. Also keep the constraint at "Logical AND."

Hope this helps.

Using the precedence constraint

  • The precedence constraint was set to "Success" deliberately. Besides, changing it to "Completion" doesn't address the question I asked or the task I was struggling with. If "Test Connection" fails, this still halts the package execution. – Michael J Swart Sep 15 '16 at 13:30
  • So many pages read to finally find this answer! Thanks! – alexkovelsky Jul 27 '18 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.