Click here to Skip to main content
15,916,371 members
Articles / Web Development / ASP.NET
Article

Excecute SSIS package (DTSX) from ASP.Net

Rate me:
Please Sign up or sign in to vote.
4.67/5 (25 votes)
16 Sep 2008CPOL3 min read 268.8K   6.6K   104   29
This article demonstrate the step by step procedure to create SSIS Package and Execute the same using ASP.net
Image 1

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:

SQL
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.

    Image 2

    2.Click on Next Button.

    3.Choose Source and click next.

    Image 3

    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.

    Image 4

    4.Choose Copy data from one or more table and Click next

    Image 5

    5.Complete Wizard Screen appears and Click finish.

    Once the above steps are completed one can see the following views

    Under control Flow Tab:

    Image 6

    Under Data Flow Tab:

    Image 7

    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.

    Image 8

    Image 9

    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.

    Image 10

    Image 11

    2)Double Click on ‘Preparation SQL Task’ under Control Flow Tab.

    Image 12

    We have taken "EXEC DeleteBusinessEntry ?" this procedure is going to take input parameter @Business_ID .

    Create Storeprocedure:

    SQL
    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.

    Image 13

    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.

    Image 14

    2)Double Click on ‘Execute Sql Task’ under Control Flow Tab.

    Image 15

    We have taken "EXEC UpdateBusinessEntry ?,?" this procedure is going to take two input parameters @Business_ID /@Business_Name.

    Create Storeprocedure:

    SQL
    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.

    Image 16

    On completion of above three steps run the package .

    Image 17

    Execute SSIS Package Using ASP.Net

    The Important reference : Microsoft.SqlServer.ManagedDTS

    The below code snippet is self explanatory

    C#
           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);
                
                //Load DTSX
                package = 
    app.LoadPackage(@"D:\SSIS_ASP_NET\SSIS_ASP_NET_DEMO\SSIS_ASP_NET_DEMO\Package1.dtsx", null);
                
                //Global Package Variable
                Variables vars = package.Variables;
                vars["Business_ID"].Value = txtBusinessID.Text;
                vars["Business_Name"].Value = txtBusinessName.Text;
                
                //Specify Excel Connection From DTSX Connection Manager
                package.Connections["SourceConnectionExcel"].ConnectionString =
     "provider=Microsoft.Jet.OLEDB.4.0;data source=" + fileName + ";Extended Properties=Excel 8.0; ";
                
                //Execute DTSX.
                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=&quot;http://www.w3.org/1999/xhtml&quot; >
    <head runat=&quot;server&quot;>
        <title>Execute SSIS</title>
    </head>
    <body>
        <form id=&quot;form1&quot; runat=&quot;server&quot;>
        <div>
             <div style=&quot;text-align: left&quot;>
                <table>
                       <tr>
                        <td style=&quot;height: 26px&quot; colspan=&quot;2&quot;>
                       <h2>Execute SSIS Package</h2>     </td>
                    </tr>
                    <tr>
                        <td style=&quot;width: 100px; height: 26px&quot;>
                            <strong>
        
            Business ID</strong></td>
                        <td style=&quot;width: 100px; height: 26px&quot;>
                            <asp:TextBox ID=&quot;txtBusinessID&quot; runat=&quot;server&quot;></asp:TextBox></td>
                    </tr>
                    <tr>
                        <td style=&quot;width: 100px&quot;>
                            <strong>BusinessName</strong></td>
                        <td style=&quot;width: 100px&quot;>
                            <asp:TextBox ID=&quot;txtBusinessName&quot; runat=&quot;server&quot;></asp:TextBox></td>
                    </tr>
                       <tr>
                        <td style=&quot;width: 100px&quot;>
                            <strong>Upload Excel</strong></td>
                        <td style=&quot;width: 100px&quot;>
        
            <asp:FileUpload ID=&quot;FileUpload1&quot; runat=&quot;server&quot; /></td>
                    </tr>   <tr>
                        <td style=&quot;width: 100px&quot;>
            </td>
                        <td style=&quot;width: 100px&quot;>
            <asp:Button ID=&quot;btnExecute&quot; runat=&quot;server&quot; Text=&quot;Execute SSIS&quot; OnClick=&quot;btnExecute_Click&quot; /></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.

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Technical Lead
    Australia Australia
    Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!
    www.santoshpoojari.blogspot.com

    Comments and Discussions

     
    QuestionHow to deploy this application in localhost Pin
    Member 1239268218-Mar-16 2:38
    Member 1239268218-Mar-16 2:38 
    QuestionError while executing Execute SQL Task Pin
    jopetcervantes1-Oct-15 19:20
    jopetcervantes1-Oct-15 19:20 
    QuestionAccess denied error Pin
    Troy Bryant14-May-15 2:47
    Troy Bryant14-May-15 2:47 
    QuestionExecuting SSIS package from remote pc Pin
    Member 40746731-May-14 1:11
    Member 40746731-May-14 1:11 
    QuestionExecute package on remote machine Pin
    Mubin M. Shaikh5-Sep-13 16:28
    professionalMubin M. Shaikh5-Sep-13 16:28 
    QuestionEncountered with an error Pin
    avixorld11-Aug-13 18:38
    avixorld11-Aug-13 18:38 
    QuestionError in exporting data from an excel file Pin
    harvey colasino16-Dec-12 21:18
    harvey colasino16-Dec-12 21:18 
    QuestionAfter publish on local IIS error has occurred Pin
    tnawalage4-Jun-12 23:41
    tnawalage4-Jun-12 23:41 
    AnswerRe: After publish on local IIS error has occurred Pin
    Member 33539239-Sep-13 9:23
    Member 33539239-Sep-13 9:23 
    QuestionPACKAGE.VARIABLES NOT AVAILABLE IN ASP.NET 4.0 Pin
    Member 470272528-May-12 1:27
    Member 470272528-May-12 1:27 
    QuestionVB.Net code? Pin
    Member 855632210-Jan-12 10:19
    Member 855632210-Jan-12 10:19 
    QuestionHi Pin
    Nitin S4-Aug-11 23:39
    professionalNitin S4-Aug-11 23:39 
    GeneralPackage on a remote machine Pin
    rocky_tar24-Jan-10 21:40
    rocky_tar24-Jan-10 21:40 
    GeneralRe: Package on a remote machine Pin
    santosh poojari1-Jun-10 20:17
    santosh poojari1-Jun-10 20:17 
    GeneralPrevent file upload Pin
    GClements7-Jan-10 3:24
    GClements7-Jan-10 3:24 
    GeneralRe: Prevent file upload Pin
    santosh poojari11-Jan-10 1:07
    santosh poojari11-Jan-10 1:07 
    QuestionExport size problem Pin
    antares_dark27-Dec-09 6:39
    antares_dark27-Dec-09 6:39 
    AnswerRe: Export size problem Pin
    santosh poojari28-Dec-09 21:16
    santosh poojari28-Dec-09 21:16 
    GeneralGood Article Pin
    Vimalsoft(Pty) Ltd25-Oct-09 21:40
    professionalVimalsoft(Pty) Ltd25-Oct-09 21:40 
    GeneralRe: Good Article Pin
    santosh poojari28-Oct-09 17:07
    santosh poojari28-Oct-09 17:07 
    GeneralRe: Good Article Pin
    Vimalsoft(Pty) Ltd28-Oct-09 20:13
    professionalVimalsoft(Pty) Ltd28-Oct-09 20:13 
    GeneralRe: Good Article Pin
    santosh poojari6-Nov-09 1:04
    santosh poojari6-Nov-09 1:04 
    GeneralRe: Good Article Pin
    Vimalsoft(Pty) Ltd6-Nov-09 1:28
    professionalVimalsoft(Pty) Ltd6-Nov-09 1:28 
    Generalfaster extraction and loading on SSIS. Pin
    blackpower2k725-Apr-09 19:54
    blackpower2k725-Apr-09 19:54 
    QuestionVariable not Found error Pin
    Pastdairyman17-Mar-09 9:26
    Pastdairyman17-Mar-09 9:26 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.