Parameterize OLEDB data connections in SSIS

In SQL Server Integration Services projects, it’s possible to parameterize OLEDB data connections so that a different Data Source (SQL Server) and Initial Catalog (Database) are used for different configurations. However, I always forget where to apply this parameterization especially for project-level connections.

First I create the project-level connection manager & project parameter, & add the parameter to the relevant configurations.

Then, I need to parameterize the connection manager. I usually start by right-clicking the connection manager in the Solution Explorer only to find that there’s no parameterization option in that menu.

Instead, the correct place to apply parameterization is in the SSIS package (even for project-level connection managers!).

I pick the relevant Property (in this case, ConnectionString) and click “Use existing parameter”. Then I pick my project parameter from the list.

Once the project is saved, the connection manager in the package will have a little fx symbol next to it to indicate that it is parameterized.

This parameterization is persisted in other packages within the same project.