Click here to Skip to main content
Click here to Skip to main content
Go to top

Deployment Of Web Application Executing SSIS Package In Web Server.

, 4 Sep 2008
Rate this:
Please Sign up or sign in to vote.
This article describes the prerequisite needed for .Net And SSIS integrated deployment.

Sample Image - maximum width is 600 pixels

Introduction

Recently I was working on the module that has SSIS package that needs to be called from ASP.net web page. We have two production server one is web server (.Net Framework 2.0- IIS 6.0) and other one is Database server (SQL Server). The challenge we face was to deployed web application calling SSIS -Dtsx package from .net environment as a file system. This article describes prerequisites required before one go on for deployment into production.

1.Deployment Checklist And Important Notes

1.Installed Integration service In Web Server

Make sure Web server in which web application is going to be deployed has Integration service installed. It does not require database to be installed. This will help to include references Microsoft.SqServer.ManagedDTS in Global Cache Assembly.

Reference :

       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;
        }
    }

        

Sample Image - maximum width is 600 pixels

Sample Image - maximum width is 600 pixels

Sample Image - maximum width is 600 pixels

2.If Migrated From SQL Server 2000 To SQL Server 2005

Database server having SQL server 2000 and SQL Server 2005 both installed in same box, make sure SQL server 2000 is stopped. Check the connection string in web application .See below if you get this error

"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.". 8/12/2008 5:31:38 PM SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. 8/12/2008 5:31:38 PM component "Destination - XXXX" (41) failed validation and returned error code 0xC020801C. 8/12/2008 5:31:38 PM One or more component failed validation. 8/12/2008 5:31:38 PM There were errors during task validation. 8/12/2008 5:31:38 PM "

Resolution to this to include instance name and port in DATA SOURCE property.

< data source=10.10.10.10/Yukon,1533;initial catalog=NorthWind;UID=xxx;PWD=xxx; Connect Timeout=300>

3.Web application having Enterprise Library

Add pooling =false in connection string to disable database connection pooling mechanism

< data source=10.10.10.10/Yukon,1533;initial catalog=NorthWind;UID=xxx;PWD=xxx; Connect Timeout=300;pooling=false>

Conclusion

Any corrections and inputs are most welcome.

License

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

Share

About the Author

santosh poojari
Technical Lead
India India
Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!
www.santoshpoojari.blogspot.com

Comments and Discussions

 
QuestionExecute SQL Task Expression Pinmemberirfunme15-Oct-12 5:58 
GeneralFailure [modified] Pinmembermallyonline20-Mar-11 5:12 
Hi Santosh, Well done on a great article.
 
My results are Failure
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
            Labelex.Text += "<br/>" + results.ToString();
 
I have no idea why but the data never gets written to the database and when I write out the results it just says failure. Any idea why this may be?
 
I changed your code ever so slightly as follows
 
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.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)
    {
        Application app = new Application();
        Package package = null;
        try
        {
            //string fileName = Server.MapPath(System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName.ToString()));
            //FileUpload1.PostedFile.SaveAs(fileName);

            string fileName = Server.MapPath("~/") + FileUpload1.PostedFile.FileName.ToString();
            FileUpload1.SaveAs(fileName);
            Labelex.Text += fileName + "<br/>"; 
 
            //Load DTSX
            package = app.LoadPackage(@"E:\inetpub\wwwroot\mysitefolder\Packages\SSIS_DEMO\Package1.dtsx", null);
            Labelex.Text += package.ToString();
 
            //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();
            Labelex.Text += "<br/>" + results.ToString();
        }
        catch (Exception ex)
        {
            Labelex.Text += "<br/>" + ex.ToString();
            
            //throw ex;
        }
        finally
        {
            //package.Dispose();
            package = null;
        }
    }
}
 

 
I am inserting to a remote sql server and verified the connection is ok in OLE DB Destination Editor. Does the remote sql server require compatibility with SSIS for this to work or is it just the 2 storeproc DeleteBusinessEntry and UpdateBusinessEntry?
 
UPDATE: I have now been able to run the package with some success from a console app using sample code from the article located Here however on checking my data it has copied export 4 times!! ?
 
Regards
Mal

modified on Sunday, March 20, 2011 12:36 PM

GeneralMissing.....Microsoft.SqServer.ManagedDTS Pinmemberjeffrey reed24-Sep-09 9:30 
GeneralRe: Missing.....Microsoft.SqServer.ManagedDTS PinmemberRavikumar Purad8-Aug-12 21:24 
GeneralProblem in accessing SSIS package after deploying web application... Pinmemberavik1023-Nov-08 2:14 
GeneralRe: Problem in accessing SSIS package after deploying web application... Pinmembermbaocha6-May-09 16:48 
GeneralRe: Problem in accessing SSIS package after deploying web application... Pinmemberpramodrenikindi21-Jul-09 18:14 
GeneralThanks for sharing PinmemberAbhijit Jana4-Sep-08 18:45 
GeneralRe: Thanks for sharing Pinmembersantosh poojari16-Sep-08 21:33 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140905.1 | Last Updated 5 Sep 2008
Article Copyright 2008 by santosh poojari
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid