Marko Radakovic

Ways to use and execute SQL Server Integration Services packages

March 13, 2014 by
SSIS packages (SQL Server Integration Services) are a part of the Microsoft SQL Server database platform and a tool for building high performance data integration and workflow applications. It is also a tool for data extraction, transformation, and loading (ETL), and can be used to automate maintenance of the SQL Server databases and updates. Basically, they can be used for moving data, with no transformations, from a variety of source types to a variety of destination types, including text files and other SQL Server instances

These packages can be stored on the Integration Services server, in the SSIS Package Store, msdb database, and in the file system, outside the location that is part of the package store

In this article, I will explain how to execute SSIS packages using:

  • SQL Server Management Studio (SSMS),
  • DTEXECUI.EXE Utility
  • DTEXEC.EXE Command Line Utility
  • SQL Server Agent Job

Executing SSIS packages using SSMS

Execution in SSMS is can be accomplished if connection is established through an instance of the Database engine that hosts the Integration Service server (Package must be stored on the Integration Service server), or if the connection is established through the Integration Server that manages an SSIS package store (A package must be stored in the package store or in the msdb database)

To execute a packages located in the Integration Services catalogs under the SSISDB node in the Object Explorer pane, right click on it and choose the Execute option. Before executing configure package execution, if needed, using settings in the Parameters, the Connection Managers and the Advanced tabs in the Execute package dialog box like shown in the image below

Execute package dialog box

Executing a package from the SSMS in other case, when connection is established through the Integration Server that manages the package store is also available. After locating a package under the Integration Services database type in the Object Explorer, simply right-click and choose the Run Package option as shown in the image below

Choosing the Run Package option

Choosing the Run Package option from the drop down list will open the Execute Package Utility window. Through several tabs on the left user can configure the Execution Options, the Reporting, the Logging options, and add configuration or command files. After configuration parameters is set, a package can be executed by clicking on the Execute button as shown in the image below

Execute Package Utility window - clicking the Execute button

There is another way to start the Execute Package Utility window, using the Command Line Interface (CLI). Syntax for this utility is: C:\&>dtexecui, that will open the Execute Package Utility window

Executing a package using this method will give the results in the Package Execution Process window. All processes, warnings, errors, and validations will be presented and can be used for further analysis

Dialog showing the Package Execution Process window

Packages in the package store, the msdb database, and the file system cannot be executed in the SSMS if the connection is established through an instance of the Database engine that hosts the Integration Services server. However, it is available to import a package to a server from this locations, and then execute it

Also, if a connection is established through the Integration Services service that manages the package store, packages stored in the Integration Services server cannot be executed. The package stored in the file system cannot be executed directly, but can be imported to the package store, and then executed

Execute SSIS packages using DTEXEC.exe Command Line Utility

There is a way to execute all packages from SSMS, no matter if they are stored on the Integration Services server, the package store, the msdb database, or in the file system, and no matter if a connection is established through the Integration Services server or the Database engine that hosts the Integration Services server

Using the dtexec command in cmd avoids connection and storing limitations. The Command Line Interface with all switches available for executing is shown in the image below

Dialog showing the CLI with all switches available for executing

For example, syntax for the package named “Package1” located on the “Test” SQL Server can be:

C:\dtexec /SQL “\Package1” /SERVER “Test”

Another example for the package named “Package1” located the file system

C:\dtexec /F “C:\User\Packages\Package1.dtsx”

Executing SSIS packages using SQL Server Agent Job

Execution can be scheduled using the SQL Server Agent Job. Before creating a new job, and scheduling, the SQL Agent Job service must be started

In the Object Explorer right click on the SQL Server Agent, under the database engine node, select New, and choose Job, from the drop down list, and the New Job window will open. In the General tab, the Name field is mandatory. To schedule a job, at least one step must be created. Steps can be created from the Steps tab clicking the New button, a window will open as shown in the image below

New job step window

The Step Name field is mandatory. From the Type drop down list, the SQL Server Integration Services Package option needs to be selected, and the SQL Server Agent Service Account from the Run as drop down list. As a package source the File system option needs to be selected, to locate a package on a local drive, and import it as step for the task. Note that more options for a package source like SQL Server, Package Store, and SSIS catalog are also available

A created job can be started manually whenever the user wants, or it can be scheduled to run automatically. Job scheduling can be set under the Schedules tab

Setting the Job scheduling under the Schedules tab

The user can set scheduling parameters by clicking the New (setting up new parameters), or Pick (running job along with already defined tasks) as shown in the image below

New Job schedules window - setting up new parameters

Dialog showing Pick Schedule for Job - SSIS example window

These are various ways to execute packages using SSMS. Also, they can be executed using SQL Business Intelligence Development Studio, using built-in stored procedures, Managed API- using types and members in the Microsoft.SqlServer.Management.IntegrationServices namespace, and Managed API – using types and members in the Microsoft.SqlServer.Dts.Runtime namespace. These will be covered in another article

Useful resources

Marko Radakovic
168 Views