DTEXEC has two execution modes when running packages in the SSIS Catalog.
By default, the process runs asynchronously – the package execution is scheduled on the server, and DTEXEC returns immediately. DTEXEC does not wait for the package execution to complete. In this case, %ERRORLEVEL% would represent whether the package execution was created successfully, and will not reflect the result of the execution.
To get the result of the package execution, you can set the $ServerOption::SYNCHRONIZED property to True. In this mode, DTEXEC will wait for the package execution to complete. If the execution was successful, the %ERRORLEVEL% will be set to 0. If it failed, it will be set to 1.
See the examples below – the first execution runs a package (2-RowCounts.dtsx) on my local instance, and completes successfully. The second execution runs a package (6-ErrorContext.dtsx) that will fail.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
C:>dtexec /ISserver SSISDBDemoMyCatalogProject2-RowCounts.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True Microsoft (R) SQL Server Execute Package Utility Version 11.0.XX.XX for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:49:16 AM Execution ID: 11. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 9:49:16 AM Finished: 9:49:22 AM Elapsed: 6.015 seconds C:>echo %ERRORLEVEL% 0 C:>dtexec /ISserver SSISDBDemoMyCatalogProject6-ErrorContext.dtsx /Par "$ServerOption::SYNCHRONIZED(Boolean)";True Microsoft (R) SQL Server Execute Package Utility Version 11.0.XXX.XX for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:49:43 AM Package execution on IS Server failed. Execution ID: 12, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 9:49:43 AM Finished: 9:49:49 AM Elapsed: 6.235 seconds C:>echo %ERRORLEVEL% 1 |
For more details on the new DTEXEC command line parameters for the SSIS Catalog, see:
- Using DTEXEC with Packages on the IS Server (SSIS Team Blog)
- dtexec Utility (Books Online)
Great post! Does this apply to all versions of SSIS or just 2012?
This post applies to 2012.
In previous versions of SSIS, DTEXEC always ran the package locally… there was no "server based execution".
Thanks for the clarification! Something to look forward to, I guess. 🙂
Has anyone else had an issue trying to run multiple packages simultaneously using Synchronous mode? I can only kick off 4 packages at a time or I begin getting timeouts connecting to the environment.
Make sure you’re using the latest service pack. We fixed a few deadlocks in SSISDB after the RTM release which could cause a package to timeout/fail if you launch more than one at the same time. If you are on the latest SP, then check where the timeout is occurring … is a problem on the SSIS catalog server, or are you opening to many connections to your remote servers?