DTS Error Handling in a DTS package

Question

Post reply

DTS Error Handling in a DTS package

PFlorenzano

SSCrazy

Points: 2120
More actions
November 7, 2003 at 8:06 am

#106910

Hello everyone,
How can I perform conditional error handling in the Execute SQL Task? For instance, I wrote a query in a seperate Execute SQL Task, indicating if one count from table a does not match the count in table b, then I would like the package to abend, else, the package to proceed. Is there something I can do programmatically for this?
Any help with this would be greatly appreciated!,
Thanks,
Peter
Peter M. Florenzano

Database Administrator

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vambati SSCrazy Points: 2769 More actions · Answer 1

Error handleing can be done using VB script or to have a condition with in the sql script that is executed.

PFlorenzano SSCrazy Points: 2120 More actions · Answer 2

With VB Script, how can I pass values from SQL Server to VB Script? Basically, I would like the package to end IF the counts from (table a) do not match (table b).

How can I code this in VB Script?

Thanks,

Pete

Peter M. Florenzano

Database Administrator

philcart SSC-Forever Points: 47794 More actions · Answer 3

You can approach this two ways,

1. Put your SQL Statement in a stored procedure and use the RAISERROR command to signal a failure condition. Then set up your workflow appropriately.

2. Store the return value from your ExecuteSQL task to a Global Variable using the Parameters button then use an ActiveXScript to interrogate the Global Variable.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

PFlorenzano SSCrazy Points: 2120 More actions · Answer 4

Phillcart,

Thank you for your help. Since I'm not all familiar with Visual Basic, I opt to use the RAISERROR approach. My stored procedure is testing for the counts of 2 tables to be within a certain percentage. If the counts of the 2 tables are less than the percentage, then the RAISERROR routing will be involked. Will the package as a whole be terminated? If not, how can I code it in T-SQL?

Thanks again,

Peter