Dynamic Connection String Passed Through C#

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

  • 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

  • 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

  • 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

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

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