How to Execute SSIS Packages in C# ASP.NET

In this s Article I am going to explain the technology that came with Sql 2005. This is SSIS which stands for Sql Server Integrated Services. Recently I was working on a project for a client with large sum of Data and there was a need to process the data on a certain time frame. There were lot of technologies available at my Disposal, but this kind of Functionality needed to be streamlined and optimised. Of all the Technologies, i have chosen SSIS. In this Article I will demonstrate on how to executing the SSIS Packages in an ASP.NET(C#).

Introduction

Sometime when we develop applications we come across a need where we need to process data that might even time to Process. Most of the time we force technology to do what it was not intend for. In most of the applications that are developed today, there is a need of importing data from different formats. You will hear people talking about export files and import files. The enterprise systems need to be able to do this in an efficient way and an optimised manner.

Background

In this s Article I am going to explain the technology that came with Sql 2005. This is SSIS which stands for Sql Server Integrated Services. Recently I was working on a project for a client with large sum of Data and there was a need to process the data on a certain time frame. There were lot of technologies available at my Disposal, but this kind of Functionality needed to be streamlined and optimised. Of all the Technologies, i have chosen SSIS. In this Article I will demonstrate on how to executing the SSIS Packages in an ASP.NET(C#).

Using the code

We are going to user C# as our language.

Creating a Package

The First thing that we need to do is to create a SSIS package. First to use SSIS you need to install SSIS that comes with your SQL installation cd. If you do not have it, you don’t need to uninstall the current Sql instance, just get the Sql cd and add additional components and choose the SSIS and Business Intelligence. Business Intelligence will be where you will design you packages.   After you have installed the necessary software you can even open any Visual Studio you on your machine and create a new Project as depicted in the following example

After you selected the Project a dialog box will appear as depicted below
Select the Integrated Service Project and Give it a proper name. For the example sake, I will leave it with this default name and click ok and you will be presented with the Following working area as depicted in the Following diagram.

Don’t be afraid this is a piece of cake. Now let us Create an Interesting hello world Package. On your Left hand side there is a toolbar like other types of Project. Click it and let it appear and you will see a different type of Controls there, in fact you have never seen something like this. Later you will explore them on your own but now let’s take a look at them and choose one of them.

Ok Drag and drop the “Execute Sql Task” to the working area and you will have this

Now the red icon that you see there, means that there is something wrong, let is check what is wrong. Move your mouse over the control you just added and you will have a tooltip that will tell you what is wrong as depicted below

Now it says we don’t have a Connection manager. Remember before you execute any Sql statement or Procedures we need to have a connection. At the bottom you will notice that there is connection manager section. Go to it and right click you will be presented with the following options of providers.

Please select “New OLE DB Connection...”  and you will be Presented with the Following dialog box


Click on new and you will be presented with another dialog box as depicted below

Now in the Server name choose your Server name and in the “Connect to a Database name” section, choose the database and test the Connection and you will receive a success and Click ok and again to close all the dialog boxes. In the Connection Manager section you will see your connection manager added as depicted

Now we have our Connection manager but the red icon is still there. We need to link it first. Now double click Execute SQL Task control to show the properties as depicted below.
Change the Connection to point the Connection Manager and click on SQL Statement and click the (...) ellipse to have a small window where you will write your SQL statement. Write the SQL statement to add your name to the temp table. I used a solid table so that we can see the results later. Do as depicted in the following diagram and click ok.

And click ok to close the dialog boxes. Now the red icon is gone. No Errors let’s make this interesting. And see what SSIS can do. Right click on the package and select copy as depicted in the following diagram

And paste 3 Times

Now edit their Sql statement by double clicking the controls each and write Different names and last and age to the inserts but now you must not use the select into change them to do insert as depicted below

Change other Controls to do the inserts and when you are done click the First Package and you will notice a green arrow as depicted below

Drag the green arrow and drop it in the second control and the same with the second control and drop it to the third control until the last. And you should line them as I did and when you done you should see this

Now the package is ready to be tested. Press f5. If you see a Green Control it means it’s successfully executed the first step and if it’s red it means there is a problem as depicted below.
Now to see what was the problem move to the Process tab as Depicted in the Following diagram

As you see the age is a string and I treated it as a number and I missed a quote. After I corrected the problem and delete the table that is created and I ran it again and this is the Results.

Now this means our package is ready to be used.
Now right click on your Solution Explorer -> Properties -> Deployment Utilities

Change the Option “CreateDeploymentUtility” to true and click ok. Build the Project and go to the location of the Project on the File System. In the Project Folder Navigate to the bin directory -> Deployment and there will two files as depicted below

The 1kb size file is used to install the package and the other one is the package that we created. Double Click on the 1kb file and install the pack.

Click next -> Next -> Don’t change anything and Click Finish as depicted in the Following Diagram

Now your Package is installed and it is ready to be executed by a SQL Job or C# code. The Package after its installed is located in the Following files system hierarchy.

Now we are going to execute that Package in our c# code. Remember in SQL 2000 there was something called DTS’s but in SQL 2005 there is SSIS. Now we are going to see how easy it is to execute our previous package.

Start

Open Visual Studio and Create a New Website. Automatically you will have an empty page defined for you like this

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

Go to Design View and you will notice there is nothing on your page. Now open your Toolbox and add a button, ScriptManager, UpdateProgress, Updatepanel. Double click on it and you will be taken to click event of the button. Add the Following code and I will explain it.

protected void btnexecute_Click(object sender, EventArgs e)
    {

        //Start the SSIS Here

        try
        {
            Application app = new Application();

            Package package = null;

            package = app.LoadPackage(@"C:\Program Files\Microsoft SQL Server\100\DTS\Packages\Integration Services Project2\Package.dtsx", null);

            //Excute Package 
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

            if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                {

                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                    Console.WriteLine();
                }
            }

        }
        catch (DtsException ex)
        {
            Exception = ex.Message;
        }

    }

Now you will notice that some of things here are not recognised and visual studio is flagging for errors even before you build the project. Don’t panic. Right click your web project and add Reference as depicted below

And you will be taken to the dialog box that will ask you the references to add choose
Microsoft.SqlServer.Dts.Runtime
And in your code in the usings add the following
using Microsoft.SqlServer.Dts.Runtime;
And you will have an error free code.  When you are done your server side code will look like this

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Runtime;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnexecute_Click(object sender, EventArgs e)
    {

        //Start the SSIS Here

        try
        {
            Application app = new Application();

            Package package = null;
            package = app.LoadPackage(@"C:\Program Files\Microsoft SQL Server\100\DTS\Packages\Integration Services Project2\Package.dtsx", null);

            //Excute Package 
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

            if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                {

                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                    Console.WriteLine();
                }
            }

        }
        catch (DtsException ex)
        {
            Exception = ex.Message;
        }

    }
}

You can choose not to hardcode your path to the package and use a web config. Let us take time and explain the above code. First we create an object of the application class and the package and initialise the package object to null. Next we load the package into the package object.
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
In this line we execute the package as we did when we test it in part one of our article. The Results will be sent to the back to the results object and the below code will examine the result object for possible exceptions

if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                {

                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                    Console.WriteLine();
                }
            }

And if the exceptions are found, the console will tell us the problems exactly. Most of people come across permissions exceptions when they use SSIS. Unfortunately there is no book that will teach you to get rid of that, it is what you need to grow with (Experience).  Let us go back to our page design and see what is there. Your mark-up should look like this

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI" TagPrefix="asp" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        &nbsp;<asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
            <ProgressTemplate>
                Processing.......................
            </ProgressTemplate>
        </asp:UpdateProgress>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
    <asp:Button ID="btnexecute"  Text="Execute" runat="server" OnClick="btnexecute_Click" />
            </ContentTemplate>
        </asp:UpdatePanel>
    </div>
    </form>
</body>
</html>

Now as you can see above codes have an UpdateProgress and an updatePanel with some messages that will tell the user that the package is still executing. When it is done the UpdateProgress message will disappear. Now let us go to our Sql Management Studio and check if the results are correct. But before you go there, you can add this part of the code to direct the Connection Manager to this code.

package.Connections["OLEDB"].ConnectionString =  “Data Source=VUYISWA;Initial Catalog=oDirectv3;Persist Security Info=True;User ID=sa;Password=abacus" providerName="System.Data.SqlClient”

It is nice because you can even direct it to a connection string in the web config. Now Run the Application and click on the button and you will notice the message that we added in the UpdateProgress and it will disappear in seconds. After you are done let us check the data in our database and mine showed this

Conclusion

There are lot of things that you can do with SSIS. SSIS is so powerful. I created a SSIS package that ran nightly for a university in Scotland and it was amazing that I could write a c# code in a SSIS Package. It can accept input and output variables and there is more to it. I will be doing a lot of SSIS Development in my Future Project and I promise to share my knowledge about the interesting things I come along every day.  More Articles will be coming weekly from me. The N-Tier Architecture Series is coming soon.