Pre-requistes
To 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.
Introduction
Most 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
I 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 the project.
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.
You can either specify the path of the package in the web.config or you can define it in the class file while programming it.
Defining it in the code and in the web.config has its own advantages and disadvantages.
Web.config
Advantages
· Can easily change the package path before running the package, in short its dynamic.
· No machine level dependency
Disadvantages
· Can be vulnerable to the security attacks.
Source File
Advantages
· The package path and package remains secure.
Disadvantages
· The package path is hard coded and therefore cannot be changed at runtime.
I 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.
The 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.
Lets 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.
The 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
Now 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
1. Click start open Visual Studio. Click on File new project and select the project type as the ASP.NET application.
2. Give an appropriate name to the project and save it. It will add the default.aspx page and web.config to the solution.
3. I 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>
4. I 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
The 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 package location.
string pkgLocation;
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");
5. Set the .aspx page as the start page by right clicking on the .aspx page.
6. Click
on the Play button 
or press F5 to start the
debugging of the package.
7. The following window will open in the browser.
8. Click 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 executed successfully.
9. The running package can be seen in the SSMS under the integration services.
10. Now you can stop the package running from Visual Studio by pressing Shift+F5 or the square button.
