Introduction
I got a task to develop the web page that execute SQL server 2005 SSIS package.
With less knowledge of SSIS it was difficult for me to get the solution right at
first time.
But with online help I was able to crack the problem. I created
a simple DTSX package which takes spreadsheet data and pumps it into a database table
and then I execute store procedure inside the package to perform manipulation in
database table.
These whole bunch of process are then executed through asp.net. Below
I have demonstrate the steps to achieve this.
Creation of SSIS Package
Prerequisite:
1)Database table:
CREATE TABLE [dbo].[tblBusiness](
[Business_ID] [numeric](18, 0) NULL,
[Business_Name] [nvarchar](50) NULL
) ON [PRIMARY]
2)Excel data:BusinessTemplate.xls
SSIS Package Workflow design:
Invoke SQL task
to delete the tblBusiness data.
Load Excel data into database table tblBusiness.
Invoke SQL task to update database table tblBusiness.
Step 1: Load Excel data into database table tblBusiness.
1. Click on ‘SSIS Import Export Wizard’ Under ‘Project’ Menu.
2.Click on Next Button.
3.Choose Source and click next.
In our case source is Excel .The path specified will be overwritten when assigned
through .net code.
E.g. package.Connections["SourceConnectionExcel"].ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=" + tbFileName.Text + ";Extended Properties=Excel 8.0; ";
3.Choose Destination and click next.
4.Choose Copy data from one or more table and Click next
5.Complete Wizard Screen appears and Click finish.
Once the above steps are completed one can see the following views
Under control Flow Tab:
Under Data Flow Tab:
Note: In Connection Manager two connection objects appears:
Excel Connection Object:SourceConnectionExcel
SQL Server: DestinationConnectionOLEDB
Double Click on each object to verify the connection string and save it again.
Just test whether Dataflow is correctly built.If turns green then dataflow is successfully
executed .
If turns RED then there is problem with connection string.
Step 2: Create Sql Task Script to delete the database table.
1. Create Global Package level variable.These variables are input parameters to
package.
So the package having sql storeprocedure can consume these input param.
2)Double Click on ‘Preparation SQL Task’ under Control Flow Tab.
We have taken "EXEC DeleteBusinessEntry ?" this procedure is going to take input
parameter @Business_ID .
Create Storeprocedure:
CREATE PROCEDURE DeleteBusinessEntry
@Business_ID bigint
AS
BEGIN
Delete from tblBusiness where Business_ID=@Business_ID
END
3)Click on ParameterMapping so as to map Global user defined input varible to storeproc
input variable.
It is case sensitive.Also take care of the datatype and size of
the datatype.Each variable should have different ParameterName in it.
Step 2: Create Sql Task Script to update database table content that is loaded with
excel data.
Drag and Drop ‘Execute Sql Task’ from toolbox on to control flow screen.
Connect
Prepare Sql task to dataflow task.Also connect Dataflow task to SQl Script task
object.
2)Double Click on ‘Execute Sql Task’ under Control Flow Tab.
We have taken "EXEC UpdateBusinessEntry ?,?" this procedure is going to take two
input parameters @Business_ID /@Business_Name.
Create Storeprocedure:
CREATE PROCEDURE UpdateBusinessEntry
@Business_ID bigint ,
@Business_Name varchar(100)
AS
BEGIN
Update tblBusiness
Set Business_Name=@Business_Name
where Business_ID=@Business_ID
END
3)Click on ParameterMapping so as to map Global user defined input varible to storeproc
input variable.
It is case sensitive.Also take care of the datatype and size of
the datatype.
Each variable should have different ParameterName in it.
On completion of above three steps run the package .
Execute SSIS Package Using ASP.Net
The Important reference : Microsoft.SqlServer.ManagedDTS
The below code snippet is self explanatory
using Microsoft.SqlServer.Dts.Runtime;
protected void btnExecute_Click(object sender, EventArgs e)
{
Application app = new Application();
Package package = null;
try
{
string fileName =
Server.MapPath(System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName.ToString()));
FileUpload1.PostedFile.SaveAs(fileName);
package =
app.LoadPackage(@"D:\SSIS_ASP_NET\SSIS_ASP_NET_DEMO\SSIS_ASP_NET_DEMO\Package1.dtsx", null);
Variables vars = package.Variables;
vars["Business_ID"].Value = txtBusinessID.Text;
vars["Business_Name"].Value = txtBusinessName.Text;
package.Connections["SourceConnectionExcel"].ConnectionString =
"provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=Excel 8.0; ";
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
}
catch (Exception ex)
{
throw ex;
}
finally
{
package.Dispose();
package = null;
}
}
The important factor in above code is to assign the data source of excel file to
the SSIS package as package.Connections["SourceConnectionExcel"].ConnectionString
Html Design
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Execute SSIS</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div style="text-align: left">
<table>
<tr>
<td style="height: 26px" colspan="2">
<h2>Execute SSIS Package</h2> </td>
</tr>
<tr>
<td style="width: 100px; height: 26px">
<strong>
Business ID</strong></td>
<td style="width: 100px; height: 26px">
<asp:TextBox ID="txtBusinessID" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
<strong>BusinessName</strong></td>
<td style="width: 100px">
<asp:TextBox ID="txtBusinessName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 100px">
<strong>Upload Excel</strong></td>
<td style="width: 100px">
<asp:FileUpload ID="FileUpload1" runat="server" /></td>
</tr> <tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
<asp:Button ID="btnExecute" runat="server" Text="Execute SSIS" OnClick="btnExecute_Click" /></td>
</tr>
</table>
</div>
<br />
<br />
<br />
<br />
</div>
</form>
</body>
</html>
Reference
Before implementing this into development .Please refer this article.This article describes about the prerequisite for production deployment.http://www.codeproject.com/KB/aspnet/Deployment_SSIS_NET.aspx
Conclusion
Hope this article will serve the purpose.Any suggestions or corrections are most
welcome.
