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.
protected void btnExecute_Click(object sender, EventArgs e)
Application app = new Application();
Package package = null;
string fileName =
Variables vars = package.Variables;
vars["Business_ID"].Value = txtBusinessID.Text;
vars["Business_Name"].Value = txtBusinessName.Text;
"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)
package = null;
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>
Any corrections and inputs are most welcome.
He is presently working as tech arch in one of the leading IT company.He has total 10 years of experience in C#.net. He is a B.E graduate in Computers from Bombay University.
Most of his experiences are in designing architect for end to end solutions. His interest areas are WCF,Spring.net,Architecture- Model View Presenter,UML,Webservice,Performance Engineering/tuning,Design patterns,Generics,Enterprise Library,Regular expressions,Silverlight and WWF.