December 13, 2013 at 10:36 am
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:
xp_cmdshell dtexec /ISServer "\SSISDB\NetYield\NetYield\Buyers.dtsx" /Server ob-db01 /set \package.variables[NightlyUpdate].Value;"\"0\"" /set \package.variables[CompanyId].Value;"\"18\""
After less than a second I get the output results:
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Started: 9:25:31 AM
Execution ID: 446.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started: 9:25:31 AM
Finished: 9:25:32 AM
Elapsed: 0.592 seconds
NULL
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?
December 16, 2013 at 8:06 am
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.
December 16, 2013 at 8:20 am
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?
December 16, 2013 at 9:03 am
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?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 16, 2013 at 9:34 am
I guess my question would be: why is this an issue? In the report you attached you can see that the SSIS packages are completing successfully so what does it matter what the query window returns?
What problem are you trying to solve by running xp_cmdshell?
December 16, 2013 at 9:42 am
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.
December 16, 2013 at 11:13 am
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.
December 19, 2013 at 9:07 am
Change the job step to Operating System (CmdExec). In the Command box use your dtexec/SSIS parms: (dtexec /FILE...). Then you can use on success or on failure like you want.
December 23, 2013 at 8:09 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply