Dynamic Connection Strings - SSIS

Frank35 1 Reputation point
2021-07-07T11:38:39.787+00:00

I am trying to change a connection string in a .dtsx package that is defined using an expression, @[$Package::ControlConnectionString]. I can't locate any variables or parameters linked to it. Could someone advise how I can change the string. Thanks.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,506 Reputation points
    2021-07-08T06:38:07.62+00:00

    Hi @Frank35 ,

    Here is an article about Parameterizing Database Connection in SSIS.

    https://www.sqlshack.com/parameterizing-database-connection-sql-server-integration-services/

    It shows how to add parameter and add expression.

    You may take a reference to change the connection string.

    Regards´╝î

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October


  2. Ryan Abbey 1,171 Reputation points
    2021-07-12T00:27:48.687+00:00

    For it to be declared as "@[$Package::ControlConnectionString]" means there should be a package level parameter called "ControlConnectionString". This is then set when initiating the package call. To be configured this way would suggest there is a master package that determines what the connection string should be and that passes the string on call.

    For resolution, depends what you're actually trying to achieve. If you want to break away from the functionality above, then going with project level parameters is the standard approach, just define a "project.params" level parameter and re-associate the connection string. If the functionality is to be maintained, then create a parameter called "ControlConnectionString" and set accordingly. (I would expect there to be an error if ControlConnectionString doesn't exist but is referenced, do you have such an error?)

    0 comments No comments