Styjun

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.

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.

I'll give my fair share of experience.

I recently had a similar experience at work, our 2 main databases name's changed, and i had no issue, or downtime on the schedules.

The model we use is not the best, but for this, and for other reasons, it is quite confortable to work with. We use BAT files to pass named parameters into a "Master" Job, and basically depending on 2 parameters, the Job runs on an alternate Database/Host.

The model we use is, in every KTR/KJB we use a variable $host and $dbname, these parameters are passed with each BAT file. So when we had to change the names of the hosts and databases, it was a simple Replace All Text Match in NotePad++, and done, 2.000+ BAT Files fixed, and no downtime.

Having a variable for the Host/DB Name for both Client Connection and Logging Connection lets you have that flexibility when things change radically.

You can also use the kettle.properties file for the logging connection.