SSIS - several configurations

Question

I have inherited the SSIS project. I've never worked with SSIS before, and one thing that seems strange to me is that there is no way to manage multiple configurations.

For each SSIS package, we have 3 delpoyment, DEV, UAT, and PRODUCTION environments.

At the moment, I need to edit the configuration for each package that we deploy manually for each change (and there are many packages).

Does anyone know a more graceful way to handle these configuration changes?

Answer 1

I'm not sure if I fully understood your setup, but we can handle this - use configuration files that are shared by packages - that is, the same configuration file is used by several packages - one for each database connection (we don’t need share configurations for other types of objects). These files typically configure the database server name and database name.

This means that each package will have several configuration files - one for each connection to the database, as well as for setting other parameters specific to the package.

(Note that you are not limited to using files for configuration - you can also use registry keys, environment variables, or a database table to store them)

It is also possible to override package configuration parameters at run time using command line arguments.

This article provides a good introduction to SSIS configurations.

Answer 2

In addition to what Ed describes, you can simply support different SQl agent jobs for different environments and change parameters from the SQL Job Step editor.

This is an easy way to do this, but I found that configuration files are best suited for long-term use.