Stamey (8/17/2012)

---

I have a package that uses a for-each loop to connect to a list of servers to transfer various pieces of data for DBA administration stuff from the servers to a central database we use for reporting. It has been failing and when I looked at it I found that while it has error handlers on each of the data flow objects, and it does log error info about failing connections, it's not handling the errors correctly to keep the entire package from failing.

My solution was to add a script and a sequence container. The script, (thanks Jamie Thomson) takes the connection for that particular loop and tests it to see if the server is available and writes a record to the error table if it is not. I am also able to extract the exact connection string and insert it into that record. That should allow me to skip the sequence container if my script fails to make a connection to a given DB server. It does work, but as I run it in debug it turns the script red, as in failure, and continues on, and in the end all of the data flow objects end up as failures. I am trying to figure out if I can make this more elegant. While no one would see this in normal execution, it sure looks like something is failing when in debug mode and I doubt it will get better when running in a job.

What I would like to do, if possible, is to remove the task failure from the script, which it currently does if it cannot make a connection to a given server, have the script allow the sequence container to execute if a connection is successful and just skip it if not, The script already logs the error info so I just need to move to the next connection.

Is this possible?

Thanks,
Chris