First published on MSDN on Jul 24, 2011
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.
|
Option
|
Description
|
|
/ISSERVER
package_path
|
(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.
The
/ISSERVER
option cannot be used together with the
/DTS
or
/SQL
or
/File
option. If multiple options are specified,
dtexec
fails.
|
|
/Par[ameter]
parameter_name(type);value
|
(Optional). Sets the parameter with a specified value.
parameter_name
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 “;”.
|
|
/Envreference
reference_id
|
(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:
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
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:
/Proj[ect]
and
/Pack[age]
to specify the project path and package stream name. Parameter values can be set by
/SET
and
/CONF
. 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.
\Package.Variables[$Project::Parameter];1
\Package.Variables[$Package::Parameter];1
In SSIS SQL agent job step, dtexec is used to schedule job steps for packages from the IS server. Customers can also use T-SQL SQL Agent job step to run a package from the IS server.
To generate the script easily, you can click on the Script button of the Execute Package Dialog...
.
Note that the tool DTExecUI is not updated with the new features in dtexec.
