Test your technical skills online, appear in Virtual Interview and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Make friends on DotNetFunda.com and expand your network!
Winners

Win Prizes

Announcements
Like us on Facebook
Sponsor
Top Articles Author
Mon, 21-Apr-2014 Authors
All Time Authors
Sourav.Kayal
39750
SheoNarayan
34800
Niladri.biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

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

Vuyiswamb
Posted by under ASP.NET AJAX category on for Beginner level | Views : 59194 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
Found interesting? Add this to:




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 programmer and a moderator in ".NetFunda. Vuyiswa has been developing for 9 years now. his major strength are C# 1.1,2.0,3.0,3.5 and sql and his interest are in Silverlight,WPF,C#,Kinect , Xbox Gaming Dev.
Login to vote for this post.

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.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.

Comment using Facebook