Search
Sponsor
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Mon, 20-Oct-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
38050
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

How to Execute SSIS Packages in C# ASP.NET Part II

Vuyiswamb
Posted by under ASP.NET AJAX category on for Beginner level | Views : 75415 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
In the previous article of this series we created an SSIS Package. In this article 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.

Using the code

We are going to user C# as our language.

Note: The first part of this series of articles is available at http://www.dotnetfunda.com/articles/article786-how-to-execute-ssis-packages-in-csharp-aspnet--part-i-.aspx

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.

Thank you for visiting DotnetFunda

Vuyiswa Maseko

Page copy protected against web site content infringement by Copyscape
About the Author

Vuyiswamb

Full Name: Vuyiswa Maseko
Member Level: NotApplicable
Member Status: Member,MVP,Administrator
Member Since: 7/6/2008 11:50:44 PM
Country: South Africa
Thank you for posting at Dotnetfunda [Administrator]
http://www.Dotnetfunda.com

Vuyiswa Junius Maseko is a Functional Head(Property Valuations) for City of Tshwane Municipality in South Africa, Gauteng and a moderator at www.DotnetFunda. Vuyiswa has been developing for 14 years now. his major strength are C# 1.1,2.0,3.0,3.5,4.0,4.5 and vb.net and sql and his interest were in asp.net, c#, Silverlight,wpf,wcf, wwf and now his interests are in Kinect. He has been using .net since the beta version of it. Vuyiswa believes that Kinect is the next generation of computing. . Thanks to people like Chris Maunder (codeproject), Colin Angus Mackay (codeproject), Dave Kreskowiak (Codeproject), Sheo Narayan (.Netfunda),Rajesh Kumar(Dell) They have made vuyiswa what he is today. Occupation: Functional Head Company: City of Tshwane Location: South Africa
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Posted by: Irfunme on: 10/16/2012 | Points: 25

Thanks for shairing this article, it's really good.

I have had the opportunity to call the SSIS package from .net application using C#. But the problem is my Execute SQL Tasks contains Expressions on the sqlSourceStament property. All my SQL Statements are coming from a table and I am storing them into a variable and I am the using the same varaible in the Expressions of Execute SQL Task. If I use Expressions in Execute SQL Task then Visual Studio is not able to evaluate my expression and it's throwing following error. If I use SQL Statements without using Expressions it is working fine. I think there is a bug a BIDS which is not able to evaluate the expressions.
Error:
"The result of the expression "SQL Statement here" on property \"SqlStatementSource\" cannot be written to the property. The expression was evaluated, but cannot be set on the property.\r\n".

Please let me know if you have any solution for this.

rfan

Login to post response

Comment using Facebook(Author doesn't get notification)