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.
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?
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato