SSIS 2012 – View Connection Manager Information for Past Executions

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.

There are three catalog views that we need to use to get at the connection strings used in an execution: catalog.executions, catalog.event_messages, catalog.event_message_context.

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.
SELECT e.package_name,
        e.start_time,
        e.end_time,
        e.status,
        emc.package_path,
        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;

In my case above, I used verbose logging to capture the connection string information for a successful execution (e.status = 7). The output is below (click on image to see the full view):
Connection String Output Example

3 thoughts on “SSIS 2012 – View Connection Manager Information for Past Executions

Comments are closed.