sql - 在其执行存储过程中查找Execute SQL任务组件ID

Question

我有一个日志存储过程，最初设计时没有与 SSIS 包系统变量相关的输入参数。现在，如果通过包执行，我想用它来获取其组件 ID(即，它的执行 SQL 任务组件 GUID)。

由于存储过程在整个项目中几乎无处不在(甚至在 MSBI 范围之外)，既不更改存储过程参数设置，也不建立任何硬编码映射关系，也不将所有执行 SQL 任务转移到脚本任务并使用 Dts.Events.Fireinformation 听起来很简单。

但是，SSIS 内置的日志记录功能在项目中已经很好地启用了。我怎样才能做到这一点？

(引用:使存储过程 RAISERROR('message', 10, 1) 或 PRINT 1 不触发 ONINFORMATION 事件在包中，因此不能使用 dbo.sysssislog 来执行此操作)。

Answer 1

以下解决方案假定执行 sql 任务不是异步执行的，并且您在任务名称中有一个“执行 sql”字符串。在目录中运行包时，我还使用默认的“ISServerExec”应用程序名称，您应该在连接字符串中将应用程序名称设置为项目的特定值，然后改用它。这些是很多假设，但是你设置的场景太开放了。

根据上述所有假设，您可以在 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

无论您要完成什么，我都建议您只使用 SSIS 日志记录功能。