如何将SQL脚本转换为SSIS中的执行进程任务的参数格式 [英] How to convert SQL script to Arguments format for Execute Process Task in SSIS

问题描述

我有主程序包调用另一个程序包，但执行时失败。以下是返回的错误消息：

I have master package calling another package, but it failed when executing. Below is the error message returned:

[Execute Process Task] Error: In Executing 
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" "
/FILE "\\140.131.12.25\Work\Platform\Db\SSIS\TABLE_LOAD.dtsx" 
/SET \Package.Variables[User::controlCount].Properties[Value];"0" 
/SET \Package.Variables[User::fileID].Properties[Value];"1" 
/SET \Package.Variables[User::filePathName].Properties[Value];"\\140.131.12.25\work\_data_inbox\DHM\Compliance\Work\test.txt" 
/SET \Package.Variables[User::archivePath].Properties[Value];"\\140.131.12.25\work\_data_inbox\DHM\Compliance\Work\Archive\" 
/SET \Package.Variables[User::stageID].Properties[Value];"24" 
/SET \Package.Variables[User::MasterExceptionPkg].Properties[Value];"" 

/Conn OH_STAGE;Provider=SQLNCLI10.1;Server=140.131.12.25;Database=OH_STAGE;Trusted_Connection=yes;" at "", The process exit code was "6" while the expected was "0".

这是我在Execute Process Task中使用的参数：

Here is the Argument I was using in Execute Process Task:

/FILE "" /SET \Package.Variables[User::controlCount].Properties[Value];"0" /SET \Package.Variables[User::fileID].Properties[Value];"23" /SET \Package.Variables[User::filePathName].Properties[Value];"" /SET \Package.Variables[User::archivePath].Properties[Value];"" /SET \Package.Variables[User::stageID].Properties[Value];"26" /SET \Package.Variables[User::MasterExceptionPkg].Properties[Value];

我使用下面的脚本调试问题，发现问题可能是因为密码保护级别。所以我想知道如何在参数中添加下划线部分。我不确定这是导致失败的原因，但仍值得尝试。当我在SSMS中单独运行脚本时，它成功调用另一个包。

I use below script to debug the issue, found out the issue might be because of the password protect level. So I am wondering how do I add underline part to the Arguments . I am not sure that's the reason causing the failure, but still worth to try. When I run below script alone in SSMS, its calling another package successfully.

DECLARE @SQLQuery AS VARCHAR(2000)
Declare @param1 varchar(200)
Declare @param2 varchar(200)
Declare @param3 varchar(200)
Declare @param4 varchar(200)
Declare @param5 varchar(200)
Declare @param6 varchar(200)

SET @SQLQuery = 'DTExec /FILE "\\140.131.12.25\Work\Platform\Db\SSIS\TABLE_LOAD.dtsx" /DECRYPT "Password1"'

set @param1=' /SET \Package.Variables[User::controlCount].Properties[Value];"0"'
set @param2=' /SET \Package.Variables[User::fileID].Properties[Value];"23"'
set @param3=' /SET \Package.Variables[User::filePathName].Properties[Value];"\\140.131.12.25\work\_data_inbox\DHM\Compliance\Work\test.txt"'
set @param4=' /SET \Package.Variables[User::archivePath].Properties[Value];"\\140.131.12.25\work\_data_inbox\DHM\Compliance\Work\Archive\"'
set @param5=' /SET \Package.Variables[User::stageID].Properties[Value];"26"'
set @param6=' /SET \Package.Variables[User::MasterExceptionPkg].Properties[Value];""'

SET @SQLQuery = @SQLQuery+@param1+@param2+@param3+@param4+@param5+@param6

EXEC master..xp_cmdshell @SQLQuery
GO

同时，我很想听听您的任何想法。非常感谢

推荐答案

您是否尝试以32位而不是64位（SSIS默认值）运行您的软件包？

Have you try to run your package in 32bit instead of 64bit (SSIS Default)?

这篇关于如何将SQL脚本转换为SSIS中的执行进程任务的参数格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！