Monday, July 22, 2013

Execute SSIS package from ASP.NET

Execute SSIS package from ASP.NET web forms. Let’s take a scenario, we will create a table in SQL server and insert data into that table using SSIS package and call the package from Web forms.
 
Step 1
 
Create a table in SQL server named “stud”.
 
 
 
Step 2
 
Prepare a parameterize insert SQL query.
 
 
  
Step 3
 
Create a SSIS package. Open SQL Server Business Intelligence Development Studio. Then create a new “Integration Services Project”.
Step 4
 
Drag a “Execute SQL Task” control in Control Flow tab.
 
 
Step 5
 
Right click on “Execute SQL Task” and select Edit. And Execute SQL Task Editor will popup.
 
Step 6
 
Now we need to configure Connection Type and Connection. For this case we use ADO.NET. And Configure the Connection.





 
 
Step 7
 
After configuration the connection type and connection string, now it’s time to configure “SQL Statement” and Parameter Mapping. Put the prepared  parameterize insert SQL query in “SQL Statement” block.
Before Parameter mapping done we need to create two variable so that we map them with parameter. For this right click on any blank space and create two string type variable Name and Roll.
 



 
Now Go to Parameter mapping and map these two Variable with  our two parameter.
Your are done with SSIS package. Now make a quick build and check everything is ok. You will find our SSIS package under Bin folder (“Package.dtsx).
 
Step 8
 
Now create and an Asp.Net web application and call the SSIS Package. Copy the Package.dtsx file to our solution. Take two textbox and a button control.
 
 
On the click event of the Button write the following code.
 
First off all you need to take reference of “Microsoft.SqlServer.ManagedDTS.dll”. You will find it in GAC.
“C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\10.0.0.0__89845dcd8080cc91”
Now include the namespace Microsoft.SqlServer.Dts.Runtime
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  
Its time to test. Run the application.  With some input value .
 
 
SQL Tables
 
 
Mission accomplished.
 
 
 

Downloads

 
You can download examples and source code from here.