SSIS Connection Reach
Overview
“We are turning off prod-sql07 on the fourteenth” is a question nobody in an ETL estate can answer quickly, and it is one join away from being a list.
Where to find it
Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Connection Reach.
Three places name a destination, and the page reads all three
catalog.object_parameters, where the name starts CM
That prefix is how the catalog exposes a connection manager’s properties.
CM.Warehouse.ConnectionString, CM.Warehouse.ServerName and CM.Warehouse.InitialCatalog are the same connection manager described three ways, and the page folds them back together.
catalog.environment_variables
Because a well built project parameterises the connection and the environment supplies it, which means the actual server name is in the environment and not in the project at all.
catalog.event_message_context
Where a failure captured the connection string as it stood.
That one is the only source that reflects what a package really connected to rather than what it was configured to connect to, and it only exists where something failed.
This is a floor, not a total
A connection hard coded inside a package lives in the .ispac and SSISDB cannot see it.
The caveat line says so on every run of the page.
That makes the finding strongest exactly where the estate is worst: the old projects with everything baked in show up as suspiciously empty.
The two views
| View | What each row is |
|---|---|
| By destination | One row per server or service the catalog names. The view for “what breaks if this goes away”. |
| By project | One row per project, with what it reaches. The view for “what does this project touch”. |
Reading the grid
| Column | What it is |
|---|---|
| Destination | The server, database, file share or service named. |
| Kind | OLE DB, ADO.NET, flat file, FTP, SMTP and the rest, from the connection manager type. |
| Named by | How many parameters or variables name it. |
| Projects | How many projects reach it. |
| Last run | The most recent execution of anything that names it. |
| Where it was found | Project parameter, environment variable, or captured at failure. The third is the only one that is evidence rather than configuration. |
| The value it came from | The parameter or variable the name was read out of. |
The toolbar
| Control | What it does |
|---|---|
| By destination / By project | The two views. |
| What it cannot see | Explains the floor: hard coded connections inside packages, and which projects look suspiciously empty. |
| Environments | Opens SSIS Environments. |
Where the data comes from
| Source | What it gives |
|---|---|
catalog.object_parameters |
Parameters whose name starts CM., which are connection manager properties. |
catalog.environment_variables |
The values an environment supplies for those parameters. |
catalog.event_message_context |
Connection strings captured at the moment of a failure. |
catalog.executions |
The last run per project, for the recency column. |
Sensitive values are masked by the catalog and read as set rather than as asterisks.
Related reports
| Report | Why you would go there |
|---|---|
| SSIS Environments | Which environment supplies which value, and what is bound to nothing. |
| SSIS Catalog Inventory | Which projects are live and which have not run in a year. |
| SSIS Package Failures | The failures whose context captured a real connection string. |
| SSIS Package Map | What calls what, for tracing the blast radius further. |
Frequently asked questions
A project shows no destinations at all. That is the strongest signal on the page. Its connections are almost certainly hard coded inside the packages, where SSISDB cannot see them, and it needs opening in SSDT.
Why do I see the same server three times? Usually a connection manager described as a connection string, a server name and an initial catalog. The page folds those together when it can match them; where the spellings differ it shows what it found rather than guessing.
Which source should I trust? The one captured at failure, because it is what actually happened. Configuration says what should happen.
Can I use this as a decommissioning checklist? As a starting point, and the caveat line says why it is not complete. Anything this page names genuinely reaches that server; it cannot promise that nothing else does.