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;";

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  September 7, 2016 at 10:19 am

  #1899617

  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.

  ---

• Kyle Sherry

  SSChasing Mays

  Points: 606

  More actions

  September 7, 2016 at 10:32 am

  #1899619

  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: 247158

  More actions

  September 7, 2016 at 10:59 am

  #1899632

  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

  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.

  ---