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

Different ways to Execute SSIS Packages
Execution of Projects and Packages

See also

To document SSIS packages consider ApexSQL Doc – a SQL Server database, server and BI documentation tool.

Marko Radakovic

Marko Radakovic

Marko is an IT and technical education teacher, who likes movies, video games, and heavy metal music.

He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too.

He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc.

View all posts by Marko Radakovic
Marko Radakovic
SSIS packages

About Marko Radakovic

Marko is an IT and technical education teacher, who likes movies, video games, and heavy metal music. He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too. He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc. View all posts by Marko Radakovic

53,767 Views
  • Bihi Abankik

    Can we use dtexec utility within a transaction if TransactionOption property is set to supported in the package ?
    Thank you 🙂

  • MarkoRadakovic

    Bihi Abankik

    Hi Bihi,

    Yes, you may use dtexec utility to execute the SSIS
    package if the TransactionOption property is set to Supported.

  • m60freeman

    Are there advantages to using DTEXEC from a Job Step instead of the SQL
    Server Integration Services Package option’s UI? I’m wondering if that
    would get more information into the Job History than we get using the
    SSIS option.