运行带有参数的SSIS包的XP_CMDSHELL

Question

我正在尝试创建一个存储过程，它将允许我将参数传递给SSIS包。

DECLARE @Path VARCHAR(200),
@ExcelPath VARCHAR(50),
@DB VARCHAR(100), 
@ExcelSheet VARCHAR(500),
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000),
@SQLServer varchar(255),
@EmailAddress varchar(255),
@DTEXEC varchar(255)


SELECT @Path = 'C:\SpyderNetwork Production\SSIS\IMPORTSC\'
SELECT @ExcelPath = 'C:\Users\Administrator\Desktop\SR\test.xlsx' 
SELECT @ExcelSheet = 'DATA$'
select @DTEXEC = 'C:\"Program Files (x86)"\"Microsoft SQL Server"\120\DTS\Binn\dtexec.exe'



exec xp_cmdshell ''

SELECT @Cmd = @DTEXEC + ' /FILE "' + @Path + 'package.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
+ ' /SET \Package.Variables[User::ExcelPath].Properties[Value];' + @ExcelPath 
+ ' /SET \Package.Variables[User::ExcelSheet].Properties[Value];' + @ExcelSheet


EXEC @ReturnCode = xp_cmdshell @Cmd

我已经用它们的值替换了参数，在命令提示符下运行，一切都正常，但当我在这里实现它时，我收到了以下错误。同样，当我可以在命令提示符下运行时，它显示我没有权限运行它，而它没有任何问题，这是没有意义的。我是不是遗漏了什么基本的东西？

Microsoft (R) SQL Server Execute Package Utility 12.0.2430.0版，适用于32位版权所有(C) Microsoft Corporation。版权所有。空启动时间: 11:29:04 AM错误: 2016-09-23 11:29:04.92代码: 0xC0016016来源:包描述:无法解密受保护的XML节点"DTS:Password“，错误为0x8009000B”密钥无效，无法在指定状态下使用“。您可能无权访问此信息。当存在加密错误时，会发生此错误。验证正确的密钥是否可用。结束错误: 2016-09-23 11:29:04.92代码: 0xC0016016来源:包描述:无法解密受保护的XML节点DTS:Password，错误0x8009000B :密钥无效，无法在指定状态下使用。您可能无权访问此信息。当存在加密错误时，会发生此错误。验证正确的密钥是否可用。结束错误: 2016-09-23 11:29:05.03代码: 0xC0202009来源:包连接管理器"TEST.SpyderDB.TESTUSER“描述: SSIS错误码DTS_E_OLEDBERROR。发生OLE DB错误。错误码: 0x80040E4D。OLE DB记录可用。来源："Microsoft SQL Server Native Client 11.0“Hresult: 0x80040E4D描述：”登录用户‘TESTUSER’失败。“结束错误: 2016-09-23 11:29:05.03代码: 0xC020801C来源:数据流任务OLE DB目标69描述: SSIS错误代码DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.对连接管理器"TEST.SpyderDB.TESTUSER“的AcquireConnection方法调用失败，错误代码为0xC0202009。在此之前可能会发布错误消息，其中包含有关AcquireConnection方法调用失败原因的更多信息。结束错误: 2016-09-23 11:29:05.03代码: 0xC0047017来源:数据流任务SSIS.Pipeline描述: OLE DB目标验证失败，返回错误码0xC020801C。结束错误: 2016-09-23 11:29:05.03代码: 0xC004700C来源:数据流任务SSIS.Pipeline描述:一个或多个组件验证失败。结束错误: 2016-09-23 11:29:05.03代码: 0xC0024107来源:数据流任务描述:任务验证过程中出现错误。结束错误警告: 2016-09-23 11:29:05.03代码: 0x80019002来源: importSCSoldUnits描述: SSIS警告代码DTS_W_MAXIMUMERRORCOUNTREACHED。执行方法成功，但引发的错误数(%5)已达到允许的最大值(%1)；导致失败。当错误数量达到MaximumErrorCount中指定的数量时，就会发生这种情况。更改MaximumErrorCount或修复错误。结束警告: 2016-09-23 11:29:05.03代码: 0x80019002来源:包描述: SSIS警告代码DTS_W_MAXIMUMERRORCOUNTREACHED。执行方法成功，但引发的错误数(%5)已达到允许的最大值(%1)；导致失败。当错误数量达到MaximumErrorCount中指定的数量时，就会发生这种情况。更改MaximumErrorCount或修复错误。结束警告DTExec:包执行返回DTSER_FAILURE (%1)。开始时间:上午11:29:04完成时间: 11:29:05已用时间: 0.172秒空

Answer 1

如果您使用的是SQL2012或更高版本，还可以使用其他不使用xp_cmdshell的解决方案。

项目应该在项目部署模型中，并部署到SSISDB目录中。

EXEC [SSISDB].[catalog].[create_execution] 
        @folder_name = N'Folder',
        @project_name = N'Test',
        @package_name = N'Package.dtsx',
        @reference_id = NULL,
        @use32bitruntime = FALSE,     
        @execution_id = @executionID OUTPUT

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @executionID,
    @object_type = 20, 
    @parameter_name = N'Var1', 
    @parameter_value = @Var1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @executionID,
    @object_type = 20, 
    @parameter_name = N'Var2', 
    @parameter_value = @Var2

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