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>
<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
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.