dynamic configuration ssdt

coool sweet 61 Reputation points
2020-08-25T16:28:55.843+00:00

HI,
i need to know if w can change provider and server name without opening and manually changing in package. i have server name in parameter but not provider (sql client 11.1)

i want to change this in 100 package , without opening it , or doing it as change parameter in sql server agent job.
is it possible ?

please let me know.

thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,575 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,856 Reputation points
    2020-08-25T17:02:02.59+00:00

    Usually, SSIS project is using a project level connection. That connection is re-used by all the SSIS packages in the project. If there is a need for a change that's the spot to go.

    0 comments
  2. coool sweet 61 Reputation points
    2020-08-25T17:09:10.43+00:00

    the prob is i have 200 packages to change . is there any way i can change it once?

  3. coool sweet 61 Reputation points
    2020-08-25T17:48:01.86+00:00

    yes my package is using package level connection that has server name as a parameter.

    now i have 200 package which all has server name as a parameter.

    but i dont want to go and change in each package.

    do we have any script that we can run in SSMS and it update parameter all package at once.

    0 comments
  4. Monalv-MSFT 5,901 Reputation points
    2020-08-26T03:30:04.66+00:00

    Hi coool sweet,

    1.Please add expression and choose the property that you need in the properties of connection manager.

    2.Then drag the parameter in the Expression of Property.

    3.After deploying these packages, we can change the value of parameter to change the value of the property when executing ssis package in catalog.

    Please refer to Parameterizing Database Connection in SQL Server Integration Services.

    Best Regards,
    Mona

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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.