CODEWITHSUNDEEP
c#sql-server-2008ssis
goofyui
goofyui

Reputation: 3492

Passing values to SSIS Connecting string from c#

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();

Upvotes: 0

Views: 3254

Answers (1)

Eric Greer
Eric Greer

Reputation: 111

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.

Upvotes: 2

Related Questions