I am redesigning a series of updates using SQL 2012 and SSIS with VS 2010. The updates currently run in a 2008R2 environment with SSIS and VS 2008. Significant changes were needed to the design so I chose to rewrite instead of converting. The packages are all running just fine. I need to still be able to call them from SQL and have the package execute as it does in the 2008 environment. Using xp_cmdshell in 2012 I execute the package fine. The problem I am having is that it reports success before the package is actually done running. Below is the SQL code that is being executed:

According to the All Executions report this package took 18.783 seconds to run. Besides SQL 2012 and VS 2010 vs. 2008 the only other change in the implementation was that now I am deploying the packages to the server instead of using MSDB.

My question is why is SQL reporting a successful completion before the package is actually done running?

the xp_cmdshell statement is executing successfully because it did complete what you sent successfully. You supplied the dtexec command to xp_cmdshell and by virtue of actually running the command it was successful. The success or failure of the SSIS job is now occurring outside of your SSMS connection.

here is a simple example.

create a batch file with a pause, thus it wont finish until you press a key.

c:\temp\test.bat

echo "hello"pause

now run the batch file in xp_cmdshell. with the pause in there and no way to interact with it the batch file will not complete but your statement will complete successfully because the batch file started.

xp_cmdshell 'c:\temp\test.bat'

even if you were to deliberately make the batch file fail, the xp_cmdshell statement will still complete successfully. I think the only way an xp_cmdshell statement will fail is if you dont have permissions to run it.

Thanks for your reply. What I don't totally understand though is when the package was running in MSDB, it wouldn't end until the package ends. Is the problem now that it's running through the package store?

It doesn't really matter WHERE the package is located. Running it via xp_cmdshell is the ENTIRE problem. Once the command shell has been invoked, SSIS is done with it. However, it may be worth doing a search to see if there might be an option or property on the execution of that statement to wait for completion, but even that might not do any good.

Wayne.Emminizer (12/16/2013)Thanks for your reply. What I don't totally understand though is when the package was running in MSDB, it wouldn't end until the package ends. Is the problem now that it's running through the package store?

It used to work though when I was running the packages in 2008R2 with MSDB instead of 2012 and the Package Store.

These initial packages are updating lookup tables. I have other packages that will need to run these packages if the lookup values don't exist in the database. The continuation of the package (as well as the path it takes) is dependent on the results of the subpackage.

Wayne.Emminizer (12/16/2013)It used to work though when I was running the packages in 2008R2 with MSDB instead of 2012 and the Package Store.

These initial packages are updating lookup tables. I have other packages that will need to run these packages if the lookup values don't exist in the database. The continuation of the package (as well as the path it takes) is dependent on the results of the subpackage.

I just may not be understanding correctly I still don't understand why you would need xp_cmdshell to do this. Are you running this in a query window manually and would like to know when one package is complete before executing another? Or is this a scheduled process?

If the latter, then you could use the SSIS step type in SQL Agent or master packages to make sure some packages complete before other start.

Thanks all for your feedback. I ended up changing the process around so that the packages were called using the Execute Package Task so as to keep it all in the family. This alleviated the need for running via command shell. Thanks again.