Exit Codes, DTEXEC, and SSIS Catalog
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.
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