When Dynamic Connection Fails, Package Succeeds but Job Fails

SQL Server Administration
1

Hi experts,
I have a 2014 package that cycles thru several MSSQL instances to collect some information. In the SSIS package, I added a Failure Constraint to insert a row to a table if the connection to that instance fails.
That works well when I run the package in Visual Studio and the package completes successfully.
BUT, when I run the Agent Job to execute the package, it Fails because a few instances could not be reached.
I don't want the job to fail in that case. How can I enable the job to reflect a Successful completion? Would using a proxy account prevent the job failure?
Thanks.

2

Add failure precedence constraint to record your row and a completion precedence constraint with the flow to going to the same task the success precedence constraint goes to.

3

Thank you, Joseph. I already had a Failure constraint and it works to insert a row into a table to trap those instance names that could not be connected to. That works well - it inserts the row, then loops back up to continue thru the remaining instances. Whether I run the package in Debug mode or run the job - it always loops thru all the instance names in the list.Today, I added a Completion precedence constraint - the job still fails if the Dynamic Connection fails. The error is "Login Timeout". Please note that when I execute the Package in Debug mode, all tasks complete successfully even when some instances cannot be reached.
Do I need to configure the job itself to handle a failure? Or do I need to add an OnFailure event handler in the package? Or something else? I appreciate your time and tips.

4

How many servers do you support with this package?

5

Approximately 100 MSSQL instances spread across 80 servers.

6

I can see why you would want to loop through them rather then having to individually add them to your package. If you are using the project deployment model you can call a package and pass in the server as a parameter and use a script task to update the connection manager on each call. So if you can make it work for a timeout scenario and a good connection, your done.