How to Run SSIS Package programmatically using Web Applications?

SSIS packages can be run from many platforms. SQL Server Agent, BIDS designer, Scheduler Jobs are the most used and familiar platforms. There are other platform that we can use to run SSIS packages such as Ms-Excel, .NET windows form, web application, WPF applications and web services. In one of the previous post, I had written about how to run SSIS package from Ms-Excel. In this post we will learn how to run SSIS package using web application.

For demonstration purpose, I have created a sample SSIS package. This SSIS package loads data from employee table to newEmployee SQL Table. We have following data into tblEmployee.

• SSIS: Read public data of Twitter Users?
• SSIS: How to pull Tweets from Twitter?
• SSIS: How to pull Currency Rates from European Central Bank
• SSIS: How to pull Stock Quotes from Google Finance
• SSIS: How to load data into SQL table from Google Spreadsheet?
• SSIS: How to Download images from Picasa album?
• SSIS: How to Load images into Picasa web album?

Execute SQL Task truncates all the rows from the newEmployee table. The Data Flow task reads all the data from OLE DB Source (employee table) and dumps it into OLE DB Destination (newEmployee table).

This package runs fine inside BIDS designer. We will try to run this package programmatically using a .NET based web application.

Microsoft has provided a Dynamic Link Library (DLL) Microsoft.SqlServer.Dts.Runtime that we can use to run our SSIS package from .NET environment. For demonstration purpose, I have created a web application and added a form in Visual Studio 2008. I have added a button and one label on the web form. The label will show the result status of the SSIS package execution.

I have added reference of Microsoft.SqlServer.Dts.Runtime dll into project. This form contains following code logic.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.SqlServer.Dts.Runtime;

namespace WebApplication1
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Button1_Click(object sender, EventArgs e)
        {
            string strpackageName;
            Package SSISpkg;
            Microsoft.SqlServer.Dts.Runtime.Application Myapp;
            DTSExecResult dtresult;
            strpackageName = @"E:\DOTNET\Employees\Employees\Package.dtsx";
            Myapp = new Microsoft.SqlServer.Dts.Runtime.Application();
            SSISpkg = Myapp.LoadPackage(strpackageName, null);
            dtresult = SSISpkg.Execute();
            Label1.Text = "Result for SSIS Pacage Execution is " + dtresult.ToString();
        }

    }
}

After setting up the code with Visual Stuio web application. I run the web application and clicked on RUN SSIS Package button. The package executed successfully and showed the rest status as Success in the label.

I went to check my newEmployee table and as expected the data were loaded into the table.

The above example can be used if our SSIS package has been stored locally in a file system. If your SSIS package is stored in a remote location or file and print server, you can use that path, the only thing you need to consider is your web application hosted machine can access that file path. The account that is running the web application under IIS has access to the file system path.