SSIS - best practices for connection managers -- compose out of parameters?

Question

I've worked a lot with Pentaho PDI so some obvious things jump out at me. I'll call Connection Managers "CMs" from here on out. Obvious, Project CMs > Package CMs, for extensability/ re-usability. Seems a rare case indeed where you need a Package-level CM. But I'm wondering another best practice. Should each Project CM itself be composed of variables? (or parameters I guess). Let's talk in concrete terms. There are specific database sources. Let's call two of them in use Finance2000 and ETL_Log_db. These have specific connection strings (password, source, etc). Now if you have 50 packages pulling from Finance2000 and also using ETL_Log_db ... well ... what happens if the databases change? (host, name, user, password?) Say it's now Finance3000. Well I guess you can go into Finance2000 and change the source, specs, and even the name itself --- everything should work then, right? Or should you simply build a project level database called "FinanceX" or whatever and make it comprised of parameters so the connectoin string is something like @Source + @ credentials + @ whatever? Or is that simply redundant? I can see one benefit of the parameter method is that you can change the "logging database" on the fly even within the package itself during execution, instead of passing parameters merely at runtime. I think. I don't know. I don't have a mountain of experience with SSIS yet. JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

SSIS, starting from version 2012, has SSIS Catalog DB. You can create all your 50 packages in one Project, and all these packages share the same Project Connection Managers. Then you deploy this Project into the SSIS Catalog; the Project automatically exposes Connection Manager parameters with CM prefix. The CM parameters are parts of the Connection Manager definition. In the SSIS Catalog you can create so called Environments. In the Environment you define variables with name and datatype, and store its value. Then - the most interesting part - you can associate the Environment and the uploaded Project. This allows you to bind project parameter with environment variable. At Package Execution - you have to specify which Environment to use when specifying Connection Strings. Yes, you can have several Environments in the Catalog, and choose when starting Package. Cool, isn't it? Moreover, passwords are stored encrypted, so none can copy it. Values of these Environment Variables can be configured by support engineers who has no knowledge of SSIS packages. More Info on SSIS Catalog and Environments from MS Docs.