Sql-server – Relating ExecutionInstanceGUID to the SSISDB

sql-server-2012ssis

The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions using the Project Deployment model will have logging to the SSISDB turned on.

When a package executes, the System::ExecutionInstanceGUID is populated with a value that, if one were using explicit logging (to sys.sysdtslog90/sys.sysssislog) would record all the events for a specific package execution.

What I'd like to know, is how do I tie an ExecutionInstanceGUID to anything in the SSISDB catalog. Alternatively, is an SSIS package executing in the SSISDB privy to the value of its catalog.executions.execution_id

Ultimately, I am trying to use the existing, custom audit table and link it back to the detailed history in the SSISDB catalog but can't seem to find the link.

Best Answer

Too much for a comment, but trying something. From the msdn page of that system table catalog.executions I get:

execution_id - bigint - The unique identifier (ID) for the instance of execution.

From this article - SSIS 2012 – View Connection Manager Information for Past Executions - I understand that:

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.

With a sample query of:

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;

What I don't see is your ExecutionInstanceGUID in this table. What I see, though, is this ancient Connect item where there's the following story:

SSIS RunningPackage.InstanceID != System::ExecutionInstanceGUID though they should be equal.

So my conclusion is that ExecutionInstanceGUID is not related to execution_id, but some InstanceId column, in case you might have one in the SSISDB.

Related Question