Executing an SSIS Package Programmatically
execute a package programatically you need to have basic knowledge of either C# or VB which are both
object oriented languages. Visual Studio 2008 SP1 or higher needs to be installed along
with SQL Server 2008 or higher.
of us are pretty familiar with SSIS package development as well as deployment.
But there are scenarios when you might have to execute package from the
front end. This might be essential in a scenario where users want to execute
packages on-demand which will necessitate the use of an application programming interface
(API). This is very possible using the tight integration of SSIS and .NET .
Executing SSIS packages programmatically
will create a sample SSIS package for this article which on execution will
open the notepad application and will display an executed successfully
notification. Microsoft has provided various classes which gives you an ability to
access every aspect of an SSIS package. To access the package programmatically
we need to add the following namespaces and add their respective references to
create a package object will require the package class to be the part of the
solution. This object will give you access to all the data members and member
functions in the class. In C# we call the data members as the variables and the
member functions as methods.
can either specify the path of the package in the web.config or you can define
it in the class file while programming it.
it in the code and in the web.config has its own advantages and disadvantages.
easily change the package path before running the package, in short its
machine level dependency
be vulnerable to the security attacks.
package path and package remains secure.
package path is hard coded and therefore cannot be changed at runtime.
prefer it defining it in the web.config as it is a best practice because if the
package execution code is to be executed from another machine it won’t create
the machine level dependency.
path defined in the web.config can be easily accessed using the configuration
manager class as this class is responsible for various members of web.config
such connection strings and appsettings.
us create a small package which will execute a task of opening a
notepad. I have created a package with a process task to call the notepad.exe
and to display a message that the package has been successfully executed after
the execution of the package.
below is the executable path of the notepad specified in the executable
section. The default path of the notepad is C:\Windows\System32\notepad.exe
I will write the code for accessing the package from a predefined location. You
may also want to dynamically traverse to the folder where the packages are
stored if you can ask the users to specify the path of the drive in which the
packages are stored in the various folders. Another method would be asking the
user to browse that particular and then execute the package which user wants.
Creating C# Project
start open Visual Studio. Click on File new project and select the project
type as the ASP.NET application.
an appropriate name to the project and save it. It will add the default.aspx
page and web.config to the solution.
have added the below code to the configuration section of the web.config file. The
following block of code fetches the connection/path of the package.
<appSettings> <add key="FilePath" value="E:\Package1.dtsx"/> </appSettings>
have added a button on the .aspx page and named it as the execute package. On
double click of this button it open a aspx.cs file. We have to specify code
behind the button in the block which you can see below
below code creates a package object of the package class and as you can see the
object can accesse all the properties of the package including the variables.
Explanation for the Block of Code
As I had said earlier that you can specify
the package path inside the .cs file. I have commented the code below which
can be hard coded as the package path.
// string pkgLocation = @"E:\Package1.dtsx";
Declare of the string variable to store
Store the package location to the string
variable by accessing the web.config file.
pkgLocation = ConfigurationManager.AppSettings["FilePath"].ToString();
Create an application object to discover
and access all the package objects.
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Create package object to execute the package.The
application class has the Loadpackage method which will load the SSIS package
from the specified location.
Package pkg = new Package(); pkg = app.LoadPackage(pkgLocation, null);
The DTSExecResult gets the state
of the package execution (Success, failure, completion, canceled) when the
Execute method is called.
DTSExecResult result = pkg.Execute(); if (result==DTSExecResult.Success) MessageBox.Show("Package Success"); else MessageBox.Show("Package failed");
the .aspx page as the start page by right clicking on the .aspx page.
on the Play button or press F5 to start the
debugging of the package.
following window will open in the browser.
on the execute package button a notepad window will open along with a package
executed successfully dialog box which means that the package has been
running package can be seen in the SSMS under the integration
10. Now you can stop
the package running from Visual Studio by pressing Shift+F5 or the square