Find Execute SQL task component ID in its executing stored procedure

Question

I have a log stored procedure that was initially designed having no input parameters related to SSIS package system variables. Now I want to use it to fetch its component ID if executed via package (i.e., its executing SQL task component GUID).

Since the stored procedure is used almost everywhere across the project (even outside of MSBI scope), neither changing stored procedure parameter setting, making any hard-code mapping relationships, nor transfer all execute SQL tasks to script tasks and use Dts.Events.Fireinformation sounds easy.

However, SSIS built-in logging feature has been well enabled in project. How can I achieve this?

(Ref: make the stored procedure RAISERROR('message', 10, 1) or PRINT 1 doesn't trigger ONINFORMATION event in package and therefore cannot use dbo.sysssislog to do this).

How often is the package ran? Can there be 2 instances of it be running at the same time? I am thinking on a solution based on the timeframe of the execution. — Fernando Sibaja

Fernando Sibaja Fernando Sibaja · Accepted Answer · 2017-04-28T16:32:25

The following solution assumes that the execute sql tasks are not executed asynchronously and that you have a "execute sql" string within the tasks names. I am also using the default 'ISServerExec' application name for when running the package within the catalog, you should set the application name to a specific value for your project within the connection string and use that instead. These are a lot of assumptions, but the scenario you are setting up is too open.

Making all the aforementioned assumptions, you could use this code within your sp:

DECLARE @taskId AS UNIQUEIDENTIFIER;

WITH hostProcess AS (

    SELECT host_process_id as id FROM SYS.dm_exec_sessions 
    WHERE program_name = 'ISServerExec' --set an specific application name here 
    AND session_id = @@SPID

)

SELECT TOP 1 @taskId= m.message_source_id
FROM SSISDB.catalog.executions e 
INNER JOIN hostProcess ON e.process_id = hostProcess.id
INNER JOIN SSISDB.catalog.event_messages m ON e.execution_id = m.operation_id
where m.message_source_name like '%execute sql%' --identifying exec sql tasks 
AND m.message_type =30 and  m.message_source_type=40
order by m.message_time DESC

IF @taskId is not null 
    BEGIN
    --do your stuff here
    END

Whaterver you are trying to accomplish I recommend using just the SSIS logging capabilities.

Find Execute SQL task component ID in its executing stored procedure

1 Answers