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
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 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
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
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
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
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
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
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
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