sql server 2008 - 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();

Answer 1

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.