如何在存储过程中调用ssis包并返回记录集？ [英] How can I call the ssis package in stored procedure and return recordset?

问题描述

我想通过ssis包集成几个XML源和sql server。

但是，如何在存储过程中调用ssis包并返回记录集？



下面的存储过程

I want to integrate several XML sources and sql server by ssis package.
but,how can I call the ssis package in stored procedure and return recordset?

Stored Procedure below

DECLARE @sqlcmd nvarchar(2000)
DECLARE @returncode int
SET @sqlcmd ='dtexec /f "D:\My Documents\Visual Studio 2005\Projects\merge\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI '
EXEC @returncode = xp_cmdshell @sqlcmd
select @returncode

存储过程返回此

我想更改返回记录集，如此



XML源代码类似于这个



我该怎么做？谢谢

the stored procedure return this
I want to change return recordset like this

XML source like a webservice like this

How can I do that? thanks

推荐答案

关注这个

1）在SSIS包中添加一个SQL TAST EXECUTOR并添加一个输出变量

2）创建SP，它将像第二张图像一样以XML格式返回结果。

3）将SP输出附加到SSIS任务中的outpout参数



现在，当您从SP调用此包时，它将返回XML字符串，然后您可以解析此XML并显示预期结果。

Follow this
1) Add one SQL TAST EXECUTOR in SSIS package and add one output variable
2) Create SP which will return result like your second image but in XML format.
3) attach your SP output to outpout parameter in SSIS Task

Now when you will call this package from your SP it will return XML string then you can parse this XML and display expected result.

这篇关于如何在存储过程中调用ssis包并返回记录集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！