Today’s post is by Terri Chen – a developer on the SQL Server Integration Services team.
In Denali, the project is a unit of packages that are deployed to the server. DTExec has been enhanced to support executing packages stored in the SSIS Catalog. The path of the package on the server can be specified, as well as the values of project parameters, package parameters and connection manager properties. Server execution options can be configured as well. All existing options are supported for back compatibility.
The main new options for executing project packages are listed in the following table.
(Optional). Loads a package that is saved on SSIS server. The package_path argument specifies the path and file name of the package. If the path or file name specified in the package_path argument contains a space, you must put quotation marks around the package_path argument.
You use /Server option together with the /ISSERVER option. Only Windows Authentication can execute a package on the SSIS Server. The current Windows user is used to access the package.
If the /Server option is omitted, the default local instance of SQL Server is assumed.
option cannot be used together with the
option. If multiple options are specified,
(Optional). Sets the parameter with a specified value.
can be a project ($Project::) or package level common parameter, connection manager parameter starting with “CM” or a server option parameter name starting with “$ServerOption::” . If the parameter type is not string, the type is specified in brackets after the parameter name. The value is separated by “;”.
(Optional). Sets the id of the referenced environment used in the execution. The parameters configured to bind to variables will use the values of the variables in the environment.
An example of executing a package from SSIS server:
The execution of SSIS server packages occurs on the server. Similar as to execute a package on the IS Server, DTExec calls catalog.create_execution, catalog.set_execution_parameter_value and catalog.start_execution to create an execution, set parameter values and start the execution. All execution logs can be seen from the server in the related views or by SSIS reports. So in addition to error messages from DTExec, the user can leverage all the utilities and tools on the server to trouble shooting the execution.
DTExec also supports executing packages from .ispac project. The related options are:
to specify the project path and package stream name. Parameter values can be set by
. To set a parameter value, reference it using the $Project or $Package namespace. Here are examples for a project level parameter and a package level parameter.