Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Looking for info on handling error with script. Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 7:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:40 PM
Points: 234, Visits: 930
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


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1346540
Posted Friday, August 17, 2012 9:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 78, Visits: 522
use C# script task to do the work.

trap the error in a loop and ignore it.



Post #1346799
Posted Monday, August 20, 2012 6:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:40 PM
Points: 234, Visits: 930
I have a VB script now, and I could ignore the error, but I'm trying to use the script to decide whether to run the objects in the sequence container, because if the connection can't be made by the script I want it to skip the sequence container so I don't have a bunch of data flow objects failing if the target server is not available.

Thanks,
Chris


Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Post #1347128
Posted Monday, August 20, 2012 8:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 78, Visits: 522
Sorry perhaps I wasn't clear. Use script to make your connections also. Do not use a component for anything. Then you will have no trouble handling an error from a connection isssue in any way you like.

ken



Post #1347202
Posted Monday, August 20, 2012 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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


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 link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1347265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse