There may come a time when you’re trying to troubleshoot a failed execution and you’re trying to determine what your dynamic connections were pointing to at the time of execution. Thankfully, this is quite easy with the SSIS Catalog in SQL Server 2012. Use the “more” link for the rest of the story.
The first thing you should obtain is the ServerExecutionID (or simply execution_id from the catalog.executions view). SSIS 2012 provides a new system variable, ServerExecutionID, for your use inside packages, so if you do any custom logging/notifications it is a good variable to include as it will be a direct pointer to the catalog views that we’ll use to find connection string information.
Catalog.executions contains one row per execution. This is where we’ll filter by execution_id.
Next, we’ll join to catalog.event_messages. This view lists all of the messages that are captured by the SSIS service with the quantity depending on the logging level set at the time of execution. You will not see any logs in this table under the Basic logging level. Under the Performance level, you’ll get OnError and OnWarning messages only, so if your package succeeds you won’t have records to use. Verbose will show everything, including diagnostic messages, and will consume quite a bit of space in your catalog, especially if you are using OLEDB providers (ADO.Net providers are not as verbose — thanks for the tip, Matt.)
To get at our connection manager attributes used at run time, such as connection strings, we will need to query the catalog.event_message_context view. This joins back to catalog.event_messages on event_message_id.
To see connection strings, a simple query might be:
DECLARE @execution_id BIGINT = 41753; -- Your execution_id/ServerExecutionID goes here.
CAST(emc.property_value AS VARCHAR(1000)) AS connection_string
FROM catalog.executions e
JOIN catalog.event_messages em
ON e.execution_id = em.operation_id
JOIN catalog.event_message_context AS emc WITH (FORCESEEK)
ON em.event_message_id = emc.event_message_id
AND emc.property_name = 'ConnectionString'
AND emc.context_type = 80 -- Connection Managers
WHERE e.execution_id = @execution_id;