执行具有多个结果集的存储过程

2023-12-02

我正在使用 SSIS 2016。

存储过程返回 4 个结果集。
只有第一个结果集是相关的，需要写入表中。
我无法修改存储过程。
我不关心其他结果集中返回的任何数据。

存储过程位于 SQL Server 2016 数据库中。结果也将驻留在 SQL Server 2016 中。

我目前在 SSIS 2008 中使用 OLE DB 源中的“SQL 命令”数据访问模式运行此进程，如下所示。我将其放在 For Each 循环容器中，以便将一系列参数值传递给存储过程，因为我每天对不同的参数值执行多次。

SET FMTONLY OFF;

EXEC myProc
     @Param1 = ?,
     @Param2 =?,
     @Param3 = ?;

默认情况下，SSIS 2008 仅返回第一个结果集，这对我有用，因为我只关心第一个结果集。

我正在使用本机 OLEDB SQL Server 客户端。据我所知，它改变了处理多个结果集的方式。我已经用过WITH RESULT SETS定义第一个结果集，但如果我执行 SSIS 将失败，表明需要定义其他结果集。

简而言之，在 SSIS 2016 中复制 SSIS 2008 中的功能的最佳方法是什么？

解决方案概述

我就这个问题做了2个实验，第一个实验表明，在没有参数的存储过程的情况下，SQL Server 2016和SSIS 2016中没有任何变化，第一个结果集被返回，其他结果集被忽略。

第二个实验表明，当使用参数时，这会抛出异常，所以你必须使用定义元数据WITH RESULT SETS选项，然后删除该选项。

详细解决方案

实验1

以下实验是使用 SQL Server 2016 和 Visual Studio 2015 以及 SSDT 2016 进行的

首先我创建了这个存储过程

CREATE PROCEDURE sp_Test


AS
BEGIN

SET NOCOUNT ON;

SELECT TOP 10 PersonType,NameStyle,Title 
  FROM [AdventureWorks2016CTP3].[Person].[Person]

SELECT  TOP 10 PersonType,Firstname,Lastname
  FROM [AdventureWorks2016CTP3].[Person].[Person_json]
END
GO

然后我向 SSIS 包添加了一个数据流任务
添加了 OLEDB 源、记录集目标
在 OLEDB 源中，我选择数据访问模式SQL command
使用以下命令
```
EXEC sp_Test
```

单击“列”选项卡时，它会显示第一个结果集结构

我们执行了它运行成功的包

实验2

我将存储过程更改为以下内容：

ALTER PROCEDURE [dbo].[sp_Test]

    @param1 varchar(10),
    @param2 varchar(10),
    @param3 varchar(10)
AS
BEGIN

    SET NOCOUNT ON;


    SELECT TOP 10 PersonType,NameStyle,Title ,@param2 as 'Param'
  FROM [AdventureWorks2016CTP3].[Person].[Person]


    SELECT  TOP 10 PersonType,Firstname,Lastname,@param3 as 'Param'
  FROM [AdventureWorks2016CTP3].[Person].[Person_json]
END

我在 OLEDB 源中使用了以下 SQL 命令：

EXEC sp_Test ?,?,?

WITH RESULT SETS (
(
    PersonType NVarchar(10),
    NameStyle NVarchar(10),
    Title  NVarchar(10),
    Param Varchar(10)
)
)

我正确映射了参数。

运行包时会抛出以下异常。

[OLE DB 源2] 错误：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80040E14。 OLE DB 记录可用。来源：“Microsoft SQL Server Native Client 11.0” Hresult：0x80040E14 描述：“EXECUTE 语句失败，因为其WITH RESULT SETS 子句指定了 1 个结果集，并且该语句尝试发送比这更多的结果集。”。

之后我尝试删除With RESULT SETS选项，所以命令是：

EXEC sp_Test ?,?,?

我尝试再次执行该包，因此执行时没有错误。

结论

尝试使用WITH RESULT SETs选项来定义OLEDB Source metadata，定义元数据后，只需删除此选项并运行包，这样它就会成功获取第一个结果集。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)