SSIS 2012 and DTEXEC Issue with xp_CmdShell

Question

SSIS 2012 and DTEXEC Issue with xp_CmdShell

jbarry 87949

Mr or Mrs. 500

Points: 576
More actions
January 24, 2014 at 1:52 pm

#396161

So, I have a series of stored procedures and SSIS packages that are being upgraded from SQL Server 2005 to SQL Server 2012. These packages are called via stored procedures using xp_cmdshell and the dtexec command. In the past (SQL 2005 and SQL 2008/R2), the dtexec utility would send output to the client as to the progress. In 2012, this is not the case. The output says to go check the package's execution from Integration Services catalog looking at the All Executions report. (See below output.)
I need the process to wait until dtexec and the ssis package is complete as in the old way because subsequent processing happens after data is extracted from a 3rd party vendor system.
I'm using the following flags\settings in my xp_cmdshell implementation:
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /ISSERVER "\SSISDB\folder\project\package.dtsx" /SERVER "myServer" /MAXCONCURRENT -1 /CHECKPOINTING OFF /REPORTING V
The above is not sending "Verbose Reporting" as it would in the past. It would wait and all the progress would be reported. How do I get the process to wait until dtexec has completed?
output
-------------------------------------------------------------------------------------------------Microsoft (R) SQL Server Execute Package Utility
Version 11.0.2100.60 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
Started: 3:35:50 PM
Execution ID: 9.
To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report
Started: 3:35:50 PM
Finished: 3:35:50 PM
Elapsed: 0.452 seconds
NULL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

jbarry 87949 Mr or Mrs. 500 Points: 576 More actions · Answer 1

I think I stumbled upon my solution (been googling most of the day so only after reaching out for help do I find my answer):

http://www.mattmasson.com/2012/02/exit-codes-dtexec-and-ssis-catalog/

The default implementation is to start the package asynchronously so you need to set it to run synchronously and then it XP_CMDSHELL will wait.

The server setting (from link above) is :

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

Many thanks to Matt Masson's tidbit!

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

Thanks for sharing your answer. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)