通过传递参数从存储过程调用SSIS包 [英] Calling SSIS package from stored procedure by passing the parameter
问题描述
存储过程语法:
创建过程[dbo]。[test_proc] @Message nvarchar(max)
as
begin
声明@SQLQuery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200)='< abcde>'
SET @SQLQuery ='DTExec / FILE ^D:\SSIS_MSMQ_Package\msmq_new\msmq_sql\msmq_sql\Package.dtsx ^'
SET @SQLQuery = @SQLQuery +'/ SET\ Package.Variables [User :: vMessage] .Value;'+ @Message +''
EXEC master..xp_cmdshell @SQLQuery
结束
SSIS包信息:
SSIS包有一个脚本任务,其中包含向MSMQ发送消息的代码
问题:
关于exec [test_proc]'< xml>< / xml>'
工作正常
但是在exec [test_proc]'
错误:
消息文本:< xml version = 1.0>
Microsoft( R) SQL Server执行包实用程序
版本11.0.3460.0 for 64位
NULL
开始时间:上午8:32:58
错误:2015-02-06 08:32:59.43 < br $>
代码:0x00000001
来源:脚本任务
描述:调用目标抛出了异常。
结束错误
DTExec:包执行返回DTSER_SUCCESS(o)。
开始:8:32:58 AM
完成:8: 32:59 AM
经历:0.531秒
NULL
问题似乎是将特殊字符传递给脚本任务变量
Stored Procedure Syntax:
create procedure [dbo].[test_proc] @Message nvarchar(max)
as
begin
declare @SQLQuery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200) = '<abcde>'
SET @SQLQuery = 'DTExec /FILE ^"D:\SSIS_MSMQ_Package\msmq_new\msmq_sql\msmq_sql\Package.dtsx^" '
SET @SQLQuery = @SQLQuery + '/SET "\Package.Variables[User::vMessage].Value";"'+ @Message + '"'
EXEC master..xp_cmdshell @SQLQuery
End
SSIS package info:
SSIS package has a script task which contains the code for sending messages to MSMQ
Problem:
On exec [test_proc] ‘<xml></xml>’
Works fine
But On exec [test_proc] ‘
Error:
Message Text:<xml version=1.0>
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3460.0 for 64-bit
Copyright (coffee) Microsoft Corporation. All rights reserved.
NULL
Started: 8:32:58 AM
Error: 2015-02-06 08:32:59.43
Code: 0x00000001
Source: Script Task
Description: Exception has been thrown by the target of an invocation.
End Error
DTExec: The package execution returned DTSER_SUCCESS (o).
Started: 8:32:58 AM
Finished: 8:32:59 AM
Elapsed: 0.531 seconds
NULL
The problem seem to be passing special characters to the script task variable
推荐答案
嗨。,
按照以下链接中提到的步骤操作:< br $> b $ b
http://www.databasejournal.com/features/mssql/executing-a-ssis-package-from-stored-procedure-in-sql-server.html [ ^ ]
这篇关于通过传递参数从存储过程调用SSIS包的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!