Execute Process Task - Not returning exit process code from SQLCMD SQL Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Execute Process Task - Not returning exit...

Execute Process Task - Not returning exit process code from SQLCMD SQL Script

Rate Topic

Display Mode

Topic Options

Author

Message

Hughesy

Posted Tuesday, November 06, 2012 1:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:46 AM
Points: 26, Visits: 352

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

Post #1381703

opc.three

Posted Thursday, November 22, 2012 2:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

Hughesy (11/6/2012)

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1387983

Hughesy

Posted Monday, November 26, 2012 1:30 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:46 AM
Points: 26, Visits: 352

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.

Post #1388824

opc.three

Posted Tuesday, December 04, 2012 11:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 6,826, Visits: 11,950

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 BigGrin

It's so much easier to handle errors than it was in 2000, which it sounds like you are all too aware Wink

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1392641

« Prev Topic | Next Topic »

Permissions