What are Different ways to Execute SSIS Package

This is short post to answer one of the interview questions " What are different ways to execute SSIS Package"?

SSIS Package can be executed by multiple ways, here are some of them.

1) By using BIDS/ SSDT

You can run your SSIS Package by using Business Intelligence Development Studio ( BIDS that is available for SSIS 2005,2008/R2) or you can use SQL Server Data Tools ( SSIS 2012 and SSIS 2014). These tools you will be using while developing your SSIS Packages, testing and debugging.

2) DtExecUI

Execute Package Utility (DtExecUI) is graphical interface to run the SSIS Packages. The Utility can run packages from different locations such as MS SQL Server Database,SSIS Package Stored or packages stored in file system.

When you connect to SSIS Instance by using SSMS and then run the package , it initiates DtExecUI. The graphical interface provide you different options to change the values of variables , connection mangers etc.

If your packages are stored in file system task and you double click the .dtsx file, it opens with DtexecUI. It is stand alone utility.

DtexecUI can be started from command line as well.

3) Dtexec.exe

Dtexec.exe is command line way to run your package. You have to provide information such as package path to run the package from command line. You can also provide the values of variables or Connection managers from command line to run the package with specific requirements.

4) SQL Server Agent Job

SQL Server Agent can be used to create job that can run the SSIS Package on demand or schedule. The SQL Server Agent Job can be single Step calling a SSIS Package or it can consist of multiple steps calling more than one SSIS Packages. In most of the companies the packages are scheduled by using SQL Server Agent. SQL Server agent can access the packages those are stored in SQL Server or from folder storage.

5) Windows Scheduler or Any third party Scheduler

Once way is to create a batch file in which you use dtexec.exe with required parameters. The batch job can be executed by Windows Scheduler or any third party scheduler.

6) Run SSIS Package Programmatically

You can run the package programmatically, here is link that can provide the code.

I have written as post how to run SSIS Package from Excel.You can check here.  What I am doing in that post, I am calling dtexec.exe to execute SSIS Package on button click that I created in Excel by using VBA. You can use any program of your choice that can start dtexec.exe to run your SSIS Package, that can be custom application.