SSIS: How to run specific task of SSIS package programmatically from windows/web application?

SSIS Packages can be run using many platforms. In a business environment SSIS packages can be run from SQL Server agent or using DTEXEC/DTEXECUI utilities. The packages can also be run from web and windows applications. Generally we run the SSIS package from first task to last task. Some time the business logic is such we want to run the selected task from SSIS package. In some situations we try to run the package task if a particular condition has been meet. For example we want to ensure the source files are available before Data Flow Task try to extract, transform and load the data.

In this post we will learn how we can run the selected/specific task of SSIS package using Windows/Web applications. For the demonstration purpose we will create two tasks in the SSIS package. We will let the user select and run either of the tasks from a windows application. The tasks are Task A and Task B in our SSIS package. Task A truncate all the data from our table. Task B is a Data Flow Task which loads the data from a flat file to SQL table.

So let us start with our table creation. We have following table named "tblProducts" in SQL Server.

CREATE TABLE [dbo].[tblProducts](
    [productName] [varchar](100) NULL,
    [quantity] [int] NULL
) ON [PRIMARY]

Let us move to BIDS and create a SSIS package. I have defined a variable called strTask of string data type. This purpose of this variable is it will hold the task name which SSIS package has to run.

• 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?

I have designed two tasks as shown in the picture below. I have added a Script Task in each of the tasks in the SSIS package. The reason is we want to put Precedence Constraint Expression which will be evaluated at the run time. The expression will see what values our variable strTask holds. If it holds the Task A name only Task A will be run.

In the Execute SQL Task I have written the TRUNCATE statement to delete all the rows from table tblProducts.

In the Data Flow Task I am reading all the data from a flat file and loading it into SQL table.

After designing the SSIS package I move to design windows applications interface. In the windows applications I have designed following interface. I have a list view which will show the task names. User will select the task name from list view and click on Run Task button. The SSIS package execution result will be show in the SSIS Task Result label.

I have added Microsoft.SQLServer.ManagedDTS Dll reference in the project. The code snippet on the windows applications is following:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            
            InitializeComponent();
            listView1.Items.Add(new ListViewItem("Task A"));
            listView1.Items.Add(new ListViewItem("Task B"));
        }

        private void button1_Click(object sender, EventArgs e)
        {

            /* This code logic will run the specific task from SSIS package*/
            string strpackageName;
            string strTaskName;
            strTaskName=listView1.SelectedItems[0].Text.ToString();
            Microsoft.SqlServer.Dts.Runtime.Package SSISpkg;
            Microsoft.SqlServer.Dts.Runtime.Application Myapp;
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult dtresult;
         
            strpackageName = @"D:\Learn_SSIS\ExcelSSIS\ExcelSSIS\Package2.dtsx";
            Myapp = new Microsoft.SqlServer.Dts.Runtime.Application();
            SSISpkg = Myapp.LoadPackage(strpackageName, null);
            Microsoft.SqlServer.Dts.Runtime.Variables AllVariabesType = SSISpkg.Variables;
            
            AllVariabesType["strTask"].Value = strTaskName;
            dtresult = SSISpkg.Execute(null,AllVariabesType,null,null,null);
            label3.Text = "Result for SSIS Pacage Execution is " + dtresult.ToString();
             
        }
    }
}

After setting up the code and user interface for windows applications I run the applications and selected Task A and clicked on Run Task button.

The result for package execution was success.

I checked the SQL table and there was no data. So all rows were deleted from the table because we run only Task A.

Next, I selected the Task B from the list and click on Run Task. This time again the package execution result was Success.

I checked the tblProducts and all the data from flat file were loaded into SQL table.

So this is how we can run the selected or specific task of a SSIS package from windows applications. The same logic can be applied to a web application.

Thanks for reading till this point.