Passing values to SSIS Connecting string from c#

Question

VS 2008 / SQL 2008

I am importing .csv file to SQL Table.

I want to pass dynamically the Source File and Destination Connection string from C# Code.

For some reasons, this code is working well but package is not executing !!!! How should i pass connection string dynamically from C# code to SSIS Package !!

string strSourceConn = Server.MapPath(filePlacedOrder.Value);
string strDestConn = System.Configuration.ConfigurationManager.AppSettings["SDB"];
string pkgLocation = Server.MapPath("Package.dtsx");

Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;

app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);

pkg.Variables["sConn"].Value = strSourceConn;
pkg.Variables["dConn"].Value = strDestConn;

pkgResults = pkg.Execute();

The above code looks reasonable. "The package is not executing." What does that mean? The package errors out? The package does not start but returns a success code anyway? The package executes without errors but does not produce any work? Does your package execute correctly in the IDE but not when you use the dynamic strings? — Sisyphus

Eric Greer Eric Greer · Accepted Answer · 2011-05-09T18:34:48

The best way to dynamically change the connection string is to retrieve the desired connection from the package and then change its connection string. This is different from setting variables with the connection information. In this case you would want to use:

pkg.Connections["sConn"].ConnectionString = strSourceConn;
pkg.Connection["dConn"].ConnectionString = strDestConn;

Where sConn and dConn are the names of the connections in your package.

Passing values to SSIS Connecting string from c#

1 Answers