Share via

Best Practice for SSIS Connection Managers

Bobby P 231 Reputation points
2025-05-23T17:51:51.5633333+00:00

What is, shall we say, best practice for configuring Configuration Managers when developing for using "Development"/"Production" file path names? Dev/Prod SQL Server Names? Etc.

I have seen using SSIS Project Parameters to do so. But this is a problem if you do not have access to SQL Server Agent to change it on the job. I think I have also seen or been made aware to use Solution Configurations within Microsoft Visual Studio to designate Dev from Prod as well.

If there are web sites and/or YouTubes that you can reference in regards to Best Practice for maintaining environment integrity in a SSIS Package and that makes Deployment relatively painless, I would GREATLY appreciate it.

Thanks in advance for your review and am hopeful for a solid answer with references.

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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 60,331 Reputation points
    2025-05-23T18:36:31.8933333+00:00

    For dev development have the connection manager point to your non-prod data. For production values the project parameters is the preferred approach. You can set the values when you deploy to your prod database or, after deployment, you can go to the Integration Services node of the server hosting your SSIS packages and set the properties there. Alternatively you can write a T-SQL script to set the values directly in the SSIS catalog but I've not gone that route myself.

    Solution configurations in VS are not the answer to this problem. They change how the project is built, not what is configured for it. These types of deployment changes should happen at deployment time.

    I don't understand what you mean by not having access to SQL Agent. SSMS can be used to manually make the changes. If you're automating this then create an environment-specific params file (or have the deployment task calculate the values) and then deploy the env-specific param file instead.

    0 comments

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.