Dynamic Connection String Passed Through C#

Question

Dynamic Connection String Passed Through C#

Kyle Sherry

SSChasing Mays

Points: 606
More actions
September 7, 2016 at 9:27 am

#400689

I'm trying to create an interface that a user can select an environment to execute a package against (development, sandbox or staging) and when they select an environment it will run the same package against any environment they selected so I want to dynamically change the connection string. That is the goal anyway.
I've parameterized my package level connection string but when I try to pass it in C# it says the parameter is read-only. Is there a way to pass connection string information with uid/pw/server info/database? What is the best way to dynamically change a connection string through C#?
Here is my call in C#
//Load DTSX
package = app.LoadPackage(PackageFullDirectory, null);
Variables myVars = package.Variables;
//CONNECTION STRING TO CONNECT DYNAMICALLY!!!
package.Variables["DatabaseConnectionVar"].Value = "Data Source=IPADDRESS;User ID=USERNAME;Initial Catalog=DATABASE;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=false;";

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Kyle Sherry (9/7/2016)
I'm trying to create an interface that a user can select an environment to execute a package against (development, sandbox or staging) and when they select an environment it will run the same package against any environment they selected so I want to dynamically change the connection string. That is the goal anyway.
I've parameterized my package level connection string but when I try to pass it in C# it says the parameter is read-only. Is there a way to pass connection string information with uid/pw/server info/database? What is the best way to dynamically change a connection string through C#?
Here is my call in C#
//Load DTSX
package = app.LoadPackage(PackageFullDirectory, null);
Variables myVars = package.Variables;
//CONNECTION STRING TO CONNECT DYNAMICALLY!!!
package.Variables["DatabaseConnectionVar"].Value = "Data Source=IPADDRESS;User ID=USERNAME;Initial Catalog=DATABASE;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=false;";

There is an alternative, maybe. Create multiple environments in SSISDB and select (at run time) the one whose variables you want the package to use.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Kyle Sherry SSChasing Mays Points: 606 More actions · Answer 2

Not a bad suggestion which I might try next. I came across this article which might address a solution. It looks promising. I'll post update if this works or not.

https://msdn.microsoft.com/en-us/library/ms136093.aspx

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Kyle Sherry (9/7/2016)

Not a bad suggestion which I might try next. I came across this article which might address a solution. It looks promising. I'll post update if this works or not.

https://msdn.microsoft.com/en-us/library/ms136093.aspx%5B/quote%5D

That looks like way too much hard work to me. Too complex for what you are trying to achieve.

The environment solution I suggested would have the added benefit of allowing you to change the values of any parameters to be passed to the package, without needing any application or coding modifications.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin