将数据写入SSISDB.Catalog.Executions

Question

我正在尝试测试一个存储过程，它从SSISDB.Catalog.Executions读取一些值。为了实现同样的目标，我想将虚拟数据写入SSISDB.Catalog.Executions。这可能吗？如果是，怎么做？

Answer 1

这个问题询问如何插入到catalog.executions中。整个catalog模式只是对具有安全角色检查的internal模式表的视图。

如果您在执行一些权限检查后查看catalog.create_execution的定义，它将使用CLR插入到internal.operations中以生成ID，然后使用实际插入到internal.executions中。

-- Parameters
DECLARE
    @folder_name nvarchar(128) = (SELECT TOP 1 F.name FROM internal.folders AS F ORDER BY 1)
,   @project_name nvarchar(128) = (SELECT TOP 1 P.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id ORDER BY F.name)
,   @package_name nvarchar(260) = (SELECT TOP 1 PKG.name FROM internal.folders AS F INNER JOIN internal.projects AS P ON P.folder_id = F.folder_id INNER JOIN internal.packages AS PKG ON PKG.project_id = P.project_id ORDER BY F.name)
,   @reference_id bigint = NULL
,   @use32bitruntime bit = 0
,   @execution_id bigint; 

DECLARE @caller_name internal.adt_sname = (SELECT USER_NAME());
DECLARE @caller_sid internal.adt_sid = (SELECT USER_ID());
DECLARE @created_time datetimeoffset;
DECLARE @return_value int;
DECLARE @operation_id bigint;
DECLARE @result bit;
DECLARE @environment_id bigint;
DECLARE @environment_found bit;
DECLARE @environment_name nvarchar(128)
DECLARE @environment_folder_name nvarchar(128)
DECLARE @reference_type char(1)
DECLARE @version_id bigint

SET @created_time = SYSDATETIMEOFFSET();
SET @operation_id = -1;

INSERT INTO
internal.operations
(
    operation_type
,   created_time
,   object_type
,   object_id
,   object_name
,   status
,   start_time
,   end_time
,   caller_sid
,   caller_name
,   process_id
,   stopped_by_sid
,   stopped_by_name
,   operation_guid
,   server_name
,   machine_name
)
VALUES
(
    200
,   SYSDATETIMEOFFSET()  -- created_time - datetimeoffset
,   20
,   0
,   N'Demo'  -- object_name - nvarchar(260)
,   1  -- status - int
,   SYSDATETIMEOFFSET()  -- start_time - datetimeoffset
,   SYSDATETIMEOFFSET()  -- end_time - datetimeoffset
,   @caller_sid
,   @caller_name
,   0  -- process_id - int
,   NULL  -- stopped_by_sid - varbinary(85)
,   N''  -- stopped_by_name - nvarchar(128)
,   NULL  -- operation_guid - uniqueidentifier
,   @@SERVERNAME
,   @@SERVERNAME
)

SELECT
    @operation_id = SCOPE_IDENTITY();

INSERT into internal.executions
(
    execution_id
,   folder_name
,   project_name
,   package_name
,   reference_id
,   reference_type
,   environment_folder_name
,   environment_name
,   project_lsn
,   executed_as_sid
,   executed_as_name
,   use32bitruntime
)
VALUES 
(
    @operation_id
,   @folder_name
,   @project_name
,   @package_name
,   @reference_id
,   @reference_type
,   @environment_folder_name
,   @environment_name
,   @version_id
,   @caller_sid
,   @caller_name
,   @use32bitruntime
);

-- Look at what I've done
SELECT @operation_id AS OperationId;