Find the Execute SQL task component id in its executable stored procedure
I have a log stored procedure that was originally designed with no input parameters associated with SSIS package system variables. Now I want to use it to retrieve its Component ID if it is executed through a package (i.e. its executable SQL Task Component GUID).
Since the stored procedure is used almost universally in the project (even outside the scope of MSBI), neither changing the parameter parameter of the stored procedure, not creating any hard-code mapping relationships, nor passing all the SQL tasks that are executed to script tasks and using Dts.Events.Fireinformation
sounds simple.
However, the built-in SSIS logging feature was well incorporated into the project. How can I achieve this?
(Ref: Make a stored procedure RAISERROR('message', 10, 1)
or PRINT 1
does not raise an event ONINFORMATION
in the package and therefore cannot use dbo.sysssislog
to do this).
source to share
The following solution assumes that the sql tasks being executed are not being executed asynchronously and that you have the "execute sql" string in the task names. I also use the default application name "ISServerExec" to run the package in the directory, you have to set the application name to a specific value for your project in the connection string and use that instead. That's a lot of speculation, but the script you're setting up is too open-ended.
Keeping all of the above assumptions, you can use this code in 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
Contrary to what you are trying to accomplish, I recommend using only SSIS logging features.
source to share