Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package Run from xp_cmdshell reports success before package is run Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:43 AM
Points: 11, Visits: 80
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?


  Post Attachments 
execution results.png (3 views, 110.02 KB)
Post #1522800
Posted Monday, December 16, 2013 8:06 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 1,494, Visits: 2,809
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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1523248
Posted Monday, December 16, 2013 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:43 AM
Points: 11, Visits: 80
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?
Post #1523259
Posted Monday, December 16, 2013 9:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 1,688, Visits: 2,271
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)

Internet ATM Machine
Post #1523285
Posted Monday, December 16, 2013 9:34 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 1,494, Visits: 2,809
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?


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1523313
Posted Monday, December 16, 2013 9:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:43 AM
Points: 11, Visits: 80
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.
Post #1523315
Posted Monday, December 16, 2013 11:13 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 1,494, Visits: 2,809
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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1523369
Posted Thursday, December 19, 2013 9:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 11:39 AM
Points: 56, Visits: 732
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.
Post #1524658
Posted Monday, December 23, 2013 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:43 AM
Points: 11, Visits: 80
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.
Post #1525558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse