SSIS 2008 Execute SQL Task Error

[William E. Booth]

Hello all,

I have a execute sql task in a container with other tasks. This is all the task has in it DECLARE @RC int
EXECUTE @RC = TruncateTables

The container has a OnError event handler that sends a email.

How is the best way to handle a error in the stored procedure? I do not seem to be able to get the SQL error code or description.

Thanks in advance for any help.
Bill

[Todd C]

William:
Is "TruncateTables" a stored procdure?

If so, can you write it so that regarless of the outcome, the last statement
is something like:
SELECT @MyError as ErrorNumber, @MyErrorDesc AS ErrorDescription

You would have to examine the @@ERROR system variable constantly and
possible use TRY / CATCH blocks.

Then set your Execute SQL task in SSIS to accept a two-field result set (1
row) and map those two field to two Package Variables.

Question: Is it really that important to know the exact error code and
message inside the package execution? Why not just set the command to
EXEC TruncateTables
and let the native SSIS error handling take care of the rest. Possibly use
logging to your advantage to log all errors.

HTH
--
Todd C
MCTS SQL Server 2005

Post by William E. Booth
Hello all,
The container has a OnError event handler that sends a email.
How is the best way to handle a error in the stored procedure? I do not seem to be able to get the SQL error code or description.
Thanks in advance for any help.
Bill
.

[William E. Booth]

Tod,
Yes it is a stored procedure. Thanks for the suggestion. I guess I just wanted to give a little more info then the task failed that I am getting now. Maybe I will try the try catch block.
Again, thanks for the response.
William

[William E. Booth]

For the archives. The problem was the error description variable did not have enough space allocated so it was not displayed.