使用SQL Server登录调用SSIS包-Java 学习之路

我正在尝试使用SQL和SSIS中的组件从.NET应用程序启动ETL过程 . 除了创建一个调用存储过程的按钮（包括传入参数）之外，我对.NET应用程序没有任何影响 . 该应用程序需要SQL Server登录才能连接到数据库 .

我遇到的问题是我无法使用SQL登录启动SSIS包（需要Windows登录），并且应用程序无法使用Windows登录（需要SQL登录） . 我如何勾选这个圆圈？

More detail

该应用程序正在管理数十个仓库维度，每个维度都使用自己的ETL流程 . 代码的设计是将Table_ID传递给存储过程，该过程创建并启动SSIS执行以运行导入 . 然后在SQL中完成ETL的其余部分 . 一个极其简化的动态SQL脚本看起来就像下面那样，动态SQL的输出如下所示：

CREATE PROCEDURE [Core].[Execute_Dimension] (@Dimension_Table_ID int) AS

-----------------------------------------
--DECLARE @Dimension_Table_ID INT  = 2001
-----------------------------------------

/************************************************
Declare variables to work with
************************************************/
DECLARE @Dimension_Table_Name   nvarchar(100)
DECLARE @SSIS_Project_Name      nvarchar(100)
DECLARE @sql_import             nvarchar(max) = N''
DECLARE @sql_transform          nvarchar(max) = N''
DECLARE @sql                    nvarchar(max)

SELECT  @Dimension_Table_Name           = [Dimension_Table_Name],
        @SSIS_Project_Name              = [SSIS_Project_Name]
FROM    [Core].[View_Dimension_Table]
WHERE   [Dimension_Table_ID] = @Dimension_Table_ID

/************************************************
Import
************************************************/
SET @sql_import = CONCAT(N'
-- ====================================================================================
-- Description:     Import Process

BEGIN TRY
    -----------------------------------------
    DECLARE @execution_id bigint
    DECLARE @SSISStatus int

    EXEC [SSISDB].[catalog].[create_execution]                  @package_name       = N''',@Dimension_Table_Name,N'_',@Dimension_Table_ID,N'.dtsx'',
                                                                @execution_id       = @execution_id OUTPUT,
                                                                @folder_name        = N''Trinity'',
                                                                @project_name       = N''',@SSIS_Project_Name,N''',
                                                                @use32bitruntime    = ''FALSE'',
                                                                @reference_id       = Null

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]     @execution_id,
                                                                @object_type        = 50,
                                                                @parameter_name     = N''SYNCHRONIZED'',
                                                                @parameter_value    = 1

    EXEC [SSISDB].[catalog].[start_execution]                   @execution_id

    --Execution has failed if SSISDB status <> 7
    SELECT @SSISStatus = [Status] FROM [SSISDB].[catalog].[executions] WHERE [execution_id] = @execution_id

    -----------------------------------------
    IF @SSISStatus = 7
    BEGIN
        PRINT ''Import: Has Succeeded''
    END
    ELSE
    BEGIN
        PRINT ''Import: Has Failed''
    END
END TRY
    BEGIN CATCH
        PRINT ''Import: Has Failed''
    END CATCH
')

/************************************************
Transform
************************************************/
SET @sql_transform = CONCAT(N'
-- ====================================================================================
-- Description:     Transform Process

BEGIN TRY
    -----------------------------------------
    EXEC [Transform].[Proc_',@Dimension_Table_Name,N'_Transform]
    -----------------------------------------
    PRINT ''Transform: Has Succeeded''
END TRY
BEGIN CATCH
    PRINT ''Transform: Has Failed''
END CATCH

')

/************************************************
Package completed
************************************************/

SET @sql = CONCAT(  @sql_import,
                    @sql_transform
                    )

EXEC (@sql)

给出如下输出：

-- ====================================================================================
-- Description:     Import Process

BEGIN TRY
    -----------------------------------------
    DECLARE @execution_id bigint
    DECLARE @SSISStatus int

    EXEC [SSISDB].[catalog].[create_execution]                  @package_name       = N'ActiveDirectoryUser_2001.dtsx',
                                                                @execution_id       = @execution_id OUTPUT,
                                                                @folder_name        = N'Trinity',
                                                                @project_name       = N'DataMart_ICT',
                                                                @use32bitruntime    = 'FALSE',
                                                                @reference_id       = Null

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]     @execution_id,
                                                                @object_type        = 50,
                                                                @parameter_name     = N'SYNCHRONIZED',
                                                                @parameter_value    = 1

    EXEC [SSISDB].[catalog].[start_execution]                   @execution_id

    --Execution has failed if SSISDB status <> 7
    SELECT @SSISStatus = [Status] FROM [SSISDB].[catalog].[executions] WHERE [execution_id] = @execution_id

    -----------------------------------------
    IF @SSISStatus = 7
    BEGIN
        PRINT 'Import: Has Succeeded'
    END
    ELSE
    BEGIN
        PRINT 'Import: Has Failed'
    END
END TRY
    BEGIN CATCH
        PRINT 'Import: Has Failed'
    END CATCH

-- ====================================================================================
-- Description:     Transform Process

BEGIN TRY
    -----------------------------------------
    EXEC [Transform].[Proc_ActiveDirectoryUser_Transform]
    -----------------------------------------
    PRINT 'Transform: Has Succeeded'
END TRY
BEGIN CATCH
    PRINT 'Transform: Has Failed'
END CATCH

2 回答

1
根据解释here，应该可以代表另一个用户或组运行脚本 . 很快解释它必须看起来像这样：
- 添加 IMPERSONATE grant
- 映射DB上的Windows和SQL服务器登录（创建用户）
- 尝试使用EXECUTE作为'windows login here'
- 执行脚本
- 不要忘记致电REVERT以避免任何意外情况和未经授权的访问
回复于 2024-04-19T09:48:20+08:00

谢谢你的回复 . 但是，在尝试实现它们时，我没有取得任何成功，特别是在脚本的SSIS元素周围 .

我运行以下......

--[domain/windowsuser] has sysadmin server role, but no explicit user asigned to individual databases

USE [DataMart_ICT]

CREATE USER [domain/windowsuser] FOR LOGIN [domain/windowsuser]
ALTER ROLE [db_owner] ADD MEMBER [domain/windowsuser]

CREATE USER [DBF_App_Admin] FOR LOGIN [DBF_App_Admin]
ALTER ROLE [db_owner] ADD MEMBER [DBF_App_Admin]

GRANT IMPERSONATE ON USER::[domain/windowsuser] TO [DBF_App_Admin]

...尝试运行我之前介绍的代码的这个小节（导致问题的位）......

USE [DataMart_ICT]
GO

    EXECUTE AS LOGIN = 'domain/windowsuser'

        -----------------------------------------
        DECLARE @execution_id bigint
        DECLARE @SSISStatus int

        EXEC [SSISDB].[catalog].[create_execution]                  @package_name       = N'ActiveDirectoryUser_2001.dtsx',
                                                                    @execution_id       = @execution_id OUTPUT,
                                                                    @folder_name        = N'Trinity',
                                                                    @project_name       = N'DataMart_ICT',
                                                                    @use32bitruntime    = 'FALSE',
                                                                    @reference_id       = Null

        EXEC [SSISDB].[catalog].[set_execution_parameter_value]     @execution_id,
                                                                    @object_type        = 50,
                                                                    @parameter_name     = N'SYNCHRONIZED',
                                                                    @parameter_value    = 1

        EXEC [SSISDB].[catalog].[start_execution]                   @execution_id

        --Execution has failed if SSISDB status <> 7
        SELECT @SSISStatus = [Status] FROM [SSISDB].[catalog].[executions] WHERE [execution_id] = @execution_id

USE [DataMart_ICT]
GO
REVERT

...并收到以下错误：

The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

问题似乎与EXEC [SSISDB] . [目录] . [start_execution]有关 . 如果我注释掉这一行就行了 . 看看这个（MS Built and defined）程序，它已经包含了自己的EXECUTE AS和REVERT，我显然无法改变它 .

如果我调用[msdb] . [dbo] . [sp_start_job]，我会得到类似的问题，如果我要调用启动SSIS包的SQL作业，我将需要它 .

回复于 2024-04-19T09:48:20+08:00

使用SQL Server登录调用SSIS包

2 回答

相关问题