sql - 如何从 SQL Server 存储过程执行 SSIS 包并传递参数

Question

我正在尝试通过存储过程执行 SSIS 包。

仅当参数被硬编码时(如代码的第一个版本中所述)，我才能成功执行该存储过程

代码的第一个版本:

SELECT @Cmd = 'DTexec /F "C:\ssis\tool\package1.dtsx" 
                      /SET "\Package.Variables[User::ConfigurationName].Properties[Value]";"Report1" 
                      /SET "\Package.Variables[User::Country].Properties[Value]";"USA" 
                      /SET "\Package.Variables[User::OrgDepartment_Team].Properties[Value]";"USA" 
                      /SET "\Package.Variables[User::Subfix].Properties[Value]";"20190503" 
                      /SET "\Package.Variables[User::TeamName].Properties[Value]";"Team1"'

在数据库中创建一条记录

但是下面的代码不起作用(我传递相同的参数值)

代码的第二个版本:

SELECT @Cmd = 'DTexec /F "C:\ssis\tool\package1.dtsx" 
                      /SET "\Package.Variables[User::ConfigurationName].Properties[Value]";"' + @ConfigurationName + 
                   '" /SET "\Package.Variables[User::Country].Properties[Value]";"' + @Country + 
                   '" /SET "\Package.Variables[User::OrgDepartment_Team].Properties[Value]";"' + @OrgDepartment_Team +
                   '" /SET "\Package.Variables[User::Subfix].Properties[Value]";"' + @Subfix + 
                   '" /SET "\Package.Variables[User::TeamName].Properties[Value]";"' + @TeamName + '"'

没有错误，但数据库中也没有创建记录。

DTExec: The package execution returned DTSER_SUCCESS (0).

如果有任何帮助，我将不胜感激:)

Answer 1

尝试找出问题

看起来您正在使用有效的语法，但我将提供一些可能有助于解决问题的建议:

(1) NULL 处理

检查命令中使用的变量是否不包含NULL值，可能会导致连接后的字符串值为NULL，可以使用ISNULL()解决问题。

SELECT @ConfigurationName = ISNULL(@ConfigurationName,'') , 
       @Country = ISNULL(@Country ,'') ,
       @OrgDepartment_Team = ISNULL(@OrgDepartment_Team,'') ,
       @Subfix  = ISNULL(@Subfix ,'') ,
       @TeamName = ISNULL(@TeamName,'')

(2) 报价处理

我更喜欢在变量中包含引号而不是主字符串。您可以使用 QUOTENAME() 函数来实现:

SELECT @ConfigurationName = QUOTENAME(ISNULL(@ConfigurationName,''),'"') , 
       @Country = QUOTENAME(ISNULL(@Country ,''),'"') ,
       @OrgDepartment_Team = QUOTENAME(ISNULL(@OrgDepartment_Team,''),'"') ,
       @Subfix  = QUOTENAME(ISNULL(@Subfix ,''),'"') ,
       @TeamName = QUOTENAME(ISNULL(@TeamName,''),'"')

SELECT @Cmd = 'DTexec /F "C:\ssis\tool\package1.dtsx" 
                      /SET "\Package.Variables[User::ConfigurationName].Properties[Value]";' + @ConfigurationName + 
                   ' /SET "\Package.Variables[User::Country].Properties[Value]";' + @Country + 
                   ' /SET "\Package.Variables[User::OrgDepartment_Team].Properties[Value]";' + @OrgDepartment_Team +
                   ' /SET "\Package.Variables[User::Subfix].Properties[Value]";' + @Subfix + 
                   ' /SET "\Package.Variables[User::TeamName].Properties[Value]";' + @TeamName 

(3) 报告

您可以通过在命令中添加 /Rep EWIP 关键字来读取整个包日志:

SELECT @Cmd = 'DTexec /F "C:\ssis\tool\package1.dtsx" /Rep EWIP '

/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]: (Optional). Specifies what types of messages to report. The available reporting options for level are as follows:

N No reporting.

E Errors are reported.

W Warnings are reported.

I Informational messages are reported.

C Custom events are reported.

D Data Flow task events are reported.

P Progress is reported.

V Verbose reporting

引用文献

• dtexec - Parameters