How to: Run a Package
To run an Integration Services package, you can use one of the following tools:
The dtexec command prompt utility (dtexec.exe).
The Execute Package Utility (dtexecui.exe).
A SQL Server Agent job.
The following sections describe how to use each of these tools to run a package.
Note
You can also run a package at design time in Business Intelligence Development Studio. For more information, see How to: Run a Package in Business Intelligence Development Studio.
Using dtexec to Run a Package
Using the dtexec utility, you can run packages that are stored in the file system, in an instance of SQL Server, or in the Integration Services Package Store. For information about the options that can be used in a dtexec command line, and some sample command lines, see dtexec Utility.
Note
On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility. If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.
To run a package by using the dtexec utility
At the command prompt, type dtexec / followed by the DTS, SQL, or File option and the package path. Make sure to include the package file name in the package path.
If the package encryption level is EncryptSensitiveWithPassword or EncryptAllWithPassword, use the Decrypt option to provide the password. If you do not inlude a password, dtexec will prompt you for the password.
(Optional) Enter additional command-line options.
Press the ENTER key.
(Optional) View logging and reporting information before closing the Command Prompt window.
Using the Execute Package Utility to Run a Package
The Execute Package Utility (dtexecui.exe) creates a command line that you can use in the following ways:
Copy to a file and use with the CommandFile option of the dtexec utility.
Paste directly in the Command Prompt window when you use the dtexec utility to run a package.
The Execute Package Utility is available only in a 32-bit version. On a 64-bit computer, any commands that the Execute Package Utility creates should also be tested in 64-bit mode before you deploy or schedule them on a production server. To test these commands in 64-bit mode, use the 64-bit version of the dtexec utility.
To run a package by using the Execute Package Utility
In Management Studio, on the View menu, click Object Explorer.
In Object Explorer, click Connect, and then click Integration Services.
Note
If you cannot connect, verify that the Integration Services service is started. The status of the service can be set to either Automatic or Manual. For more information, see How to: Set the Properties of the Integration Services Service.
Expand the Stored Packages folder and its subfolders to locate the package to run, right-click the package, and then click Run Package.
(Optional) In the Execute Package Utility dialog box, perform one or more of the following tasks:
Specify a different package to run.
Click Configurations, Command Files, Connection Managers, Execution Options, Reporting, Logging, Set Values, or Verification to update run-time options.
Click Command Line to review the command line that the utility uses.
Click Execute.
To stop the running package, click Stop in the Package Execution Progress dialog box.
When the package finishes, click Close to exit the Package Execution Progress dialog box.
Using SQL Server Agent to Run a Package
When you create a SQL Server Agent job to run packages, you must create a separate step for each package that you want to run. The job can be associated with one or more schedules, or can be an unscheduled job that you run manually.
The account that runs an Integration Services package as a SQL Server Agent job step requires all the same permissions as an account that runs the package directly. For information about how to troubleshoot packages that are run from SQL Server Agent jobs, see the Microsoft Knowledge Base article, An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.
For a video that demonstrates how to troubleshoot packages that are run from SQL Server Agent jobs, see the video home page, Troubleshooting: Package Execution Using SQL Server Agent (SQL Server Video), in the MSDN Library.
For a video that demonstrates how to use SQL Server Agent to run a package, see the video home page, How to: Automate Package Execution by Using the SQL Server Agent (SQL Server Video), in the MSDN Library.
To run a package by using a SQL Server Agent job
In SQL Server Management Studio, open the instance of SQL Server in which you want to create a job, or the instance that contains the job to which you want to add a step.
Expand SQL Server Agent and perform one of the following tasks:
To create a new job, right-click Jobs and then click New.
To use an existing job, expand Jobs, right-click the job, and then click Properties.
On the General page, if you are creating a new job, provide a job name, select an owner and job category, and, optionally, provide a job description.
To make the job available for scheduling, select Enabled.
Click Steps, and then click New.
Provide a step name and, in the Type list, select a job step type that is based on the version of the dtexec utility (dtexec.exe) that you want to run the job:
To run the job by using the version of the dtexec utility that the system automatically invokes, select SQL Server Integration Services Package.
On a 32-bit computer that is running SQL Server and SQL Server Agent, this setting invokes the 32-bit version of the dtexec utility.
On a 64-bit computer that has the 64-bit version of SQL Server and SQL Server Agent installed and running, this setting invokes the 64-bit version of the dtexec utility.
On a 64-bit computer that only has the 32-bit version of SQL Server and SQL Server Agent installed and running, this setting invokes the 32-bit version of the dtexec utility.
To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, in the New Job Step dialog box, on the Execution options tab, select Use 32 bit runtime.
In the Run as list, select the proxy account that has the credentials that the job will use. For more information, see the following topics:
On the General tab, select the package source.
If the source is SQL Server or the SSIS Package Store, provide the server name and select the authentication mode to use. Then, provide the name of the package, or click the ellipsis button (…) and locate the package.
If the source is the file system, provide the path and name of the package, or click the ellipsis button (…) and locate the package.
Important
For password-protected SSIS packages, click the Configurations tab to enter the password in the Package Password dialog box. Otherwise, the SQL Server Agent job that runs the password-protected package will fail.
To specify command-line options, do the following:
Click the Command Files tab to specify the files that contain the options that the package uses.
Click the Command Line tab to modify or restore the command-line options.
Click the Configurations tab to add configuration files and, optionally, export configured variables to a file.
Click the Data Sources tab, select the connection manager check box and then update the connection string.
Click the Execution Options tab to specify the package run-time behavior, such as whether the package fails if warnings occur.
Click the Logging tab to add log providers. Select a log provider in the Log Provider list and then type the connection string.
Click the Set Values tab to map properties and variables to values.
Note
The property path uses this syntax: \Package\<container name>.<property name>. Depending on the package structure, a container might include other containers, in which case nested containers are separated by a back slash (\). For example, Package\MyForeachLoop\MySequence\MyExecuteSQLTask.Description.
Click the Verification tab to specify whether only signed packages can run and to specify the version of package to run.
Click OK.