Execute remote ssis package using dtexec

Question

Is there a way to get the dtexec tool to not exit until the package has completed running, when pointed to a remote SSIS package?

My package runs successfully, but dtexec reports a much shorter run time than the actual duration reported in the "All Executions" report on the SQL Server. It prints the message:

To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report

My goal is to kick off dtexec on the command line, and then run other code that depends on the completion of the package. Ideally I would like dtexec to not exit until the package has completed running.

Is this possible?

billinkc billinkc · Accepted Answer · 2013-07-18T22:07:39

You are looking for the SYNCHRONIZED parameter

To make my link only answer into a link and code only answer, here's Phil's code for doing it withing TSQL

DECLARE @execution_id BIGINT = 0;

-- Create a package execution
EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx', 
        @execution_id=@execution_id OUTPUT, 
        @folder_name=N'PhilsTest', 
        @project_name=N'Demo', 
        @use32bitruntime=False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value=1; -- true


-- Start the package
EXEC [SSISDB].[catalog].[start_execution] 
        @execution_id;

To get the same behaviour from dtexec, you would specify the parameter like

DTExec 
/ISSERVER "\SSISDB\folderB\Integration Services Project17\Package.dtsx" 
/SERVER "." /Envreference 2 
/Par "$Project::ProjectParameter(Int32)";1 /Par "Parameter(Int32)";21 
/Par "CM.sqlcldb2.SSIS_repro.InitialCatalog";ssisdb 
/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

Execute remote ssis package using dtexec

1 Answers