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?
In your script, you can control its apparent success or failure via Dts.TaskResult = ?, which I am assuming you have no problem with.
What you could also do is create an int package variable - call it 'Success' or whatever - and set its value in the script. 0 = failure and 1 = success.
Then, use precedence constraints after your script task to direct the program flow, depending on the value of 'success'.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.