使用变量从 SSIS 执行流程任务运行 SQLCMD 脚本 [英] Running a SQLCMD script from SSIS Execute Process Task with variables
问题描述
我有一个包含 SQLCMD 元素的脚本的 .sql 文件.
我需要从 SSIS 包运行它.
我无法从执行 SQL 任务"中执行它.
根据这个
参数: -S server -U user -P pass -i @[User::FileNameView]
错误:
[Execute Process Task] Error: In Executing "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" "-S server -U user -P pass -i @[User::FileNameView]" at "",进程退出代码为1",而预期为0".
我的问题是:如何在 Arguments 字符串中包含变量?我应该采取不同的方法吗?
额外信息:
SQL Server 2008R2
我需要它作为变量的原因是因为我将执行进程任务"放在每个循环"中,以便能够执行文件夹中的所有 .sql 文件.
我相信您需要创建一个名为 Arguments 的表达式,其值为:
"-S server -U user -P pass -i " + @[User::FileNameView]
如果 @[User::FileNameView]
不是字符串,您可能需要进行一些类型转换.
I have an .sql file with scripts that include SQLCMD elements.
I need to have it run from an SSIS package.
I was unable to execute it from an "Execute SQL Task".
According to this http://www.sqlservercentral.com/Forums/Topic818940-147-1.aspx I should use an "Execute Process Task" with:
Executable: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE
Arguments: -S server -U user -P pass -i C:\path\to\file.sql
This is working fine.
But, I need the Arguments to include a Variable. What ever I try, it is failing.
Arguments: -S server -U user -P pass -i @[User::FileNameView]
The error:
[Execute Process Task] Error: In Executing "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" "-S server -U user -P pass -i @[User::FileNameView]" at "", The process exit code was "1" while the expected was "0".
My questions is: how do I include a variable in the Arguments string? Is there a different approach I should take?
Extra info:
SQL Server 2008R2
The reason I need this to be a variables is because I will put the "Execute Process Task" in a "For each loop" to be able to execute all .sql files in a folder.
I believe you need to create an expression named Arguments with the value:
"-S server -U user -P pass -i " + @[User::FileNameView]
You may need to do some type casting if @[User::FileNameView]
isn't a string.
这篇关于使用变量从 SSIS 执行流程任务运行 SQLCMD 脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!