November 6, 2012 at 1:45 pm
Hi
I have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL script against a target database on a remote server on the same network. It references the SQLCMD utility stored locally and uses the -o switch to output the result of the script.
The result of the script running against the target failed correctly and spat out the results to the output file and rolled back the transaction as expected, however, it did not return an exit process code value of 1 back to the calling execute process task and therefore continued the steps downstream assuming success.
I cannot understand why this is happening, particularly, as we have moved the target database to a new server and previously the task would have returned the exit process code correctly and failed the task.
Hope you can advise/assist as this is baffling me.
Thanks
Mark
November 22, 2012 at 2:10 pm
Hughesy (11/6/2012)
HiI have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL script against a target database on a remote server on the same network. It references the SQLCMD utility stored locally and uses the -o switch to output the result of the script.
The result of the script running against the target failed correctly and spat out the results to the output file and rolled back the transaction as expected, however, it did not return an exit process code value of 1 back to the calling execute process task and therefore continued the steps downstream assuming success.
I cannot understand why this is happening, particularly, as we have moved the target database to a new server and previously the task would have returned the exit process code correctly and failed the task.
Hope you can advise/assist as this is baffling me.
Thanks
Mark
When you say it "failed correctly", are you by chance catching exceptions in a TRY/CATCH block within your SQL code? When you do that, are you re-raising an exception in your CATCH block using RAISERROR with a severity of 11 or higher prior to exiting the script? If not, then sqlcmd may be viewing the batch as having been successful even though you decided to rollback all work and may sent many informational messages to the INFO output stream.
If you capture the exact sqlcmd command-line the SSIS Task is executing and you run it from a cmd shell prompt, what do you get after it finishes when you run this?
ECHO %ERRORLEVEL%
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 26, 2012 at 1:30 pm
Thanks opc! Your we're spot on. I had Raise error statements against each line with the correct level, however when passing down to the error handling at the bottom (2000 I'm afraid so no try catch) I'd missed off the raise error. Once added it works fine. Effectively the individual line raise error statements are needless.
N.B. There are Go statements to split up the dml as there is much of it (eases memory on commit using sql utility such as sqlcmd. which makes the error handling less easy.
Thanks again.
December 4, 2012 at 11:33 am
Sorry for the delay. I just returned from vacation.
I am hoping you are allowed to upgrade to 2012 soon enough so you can take advantage of the newest error handling language features (e.g. TRY/CATCH and THROW). I for one would never want to go back to the 2000 coding hoops we had to jump through 😀 It's so much easier to handle errors than it was in 2000, which it sounds like you are all too aware 😉
I am happy you got it sorted, thanks for posting back!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply