Executing an SSIS Package Programmatically


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.




Array

2 Responses to “Executing an SSIS Package Programmatically”

  1. Hi Franco,

    Nice article on the package execution. A fantastic tip to start with. Thanks again!!!

  2. Great article, fills in some of the gaps I needed, but you really should post your code with this (A zip of the project), because there’s a lot of stuff you didn’t explain… particularly, this:

    “To create a package object will require the package class to be the part of the solution.”

    I can’t use any of the information here until I can get past that step, and I’ve been trying for a few hours now but my C# project refuses to see any of the output from my SSIS project.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |