使用多个结果集执行存储过程 [英] Execute Stored Procedure with multiple result sets

问题描述

我正在使用 SSIS 2016.我需要执行一个返回 4 个结果集的存储过程.我只需要保留第一个结果集并将其写入表格.我无法修改存储过程.我不关心其他结果集中返回的任何数据.存储过程位于 SQL Server 2016 数据库中.结果也将驻留在 SQL Server 2016 中.

I am using SSIS 2016. I need to execute a stored procedure that returns 4 result sets. I only need to keep the first result set and write this to a table. I can not modify the stored procedure. I do not care about any of the data returned in the other result sets. The stored procedure is in a SQL Server 2016 database. Results will also reside in SQL Server 2016.

我目前在 OLE DB 源中使用SQL 命令"数据访问模式在 SSIS 2008 中运行此进程，如下所示.我在 For Each Loop Container 中有这个，用于将一系列参数值传递给存储过程，因为我每天针对不同的参数值多次执行它.

I currently have this process running in SSIS 2008 using the "SQL Command" data access mode in an OLE DB Source like below. I have this in a For Each Loop Container to pass a series of param values to the stored procedure as I execute it multiple times for different param values on a daily basis.

SET FMTONLY OFF;

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

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

By default SSIS 2008 is only returning the first result set, which has worked for me as I only care about the first result set.

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

I am using the Native OLEDB SQL Server client. From what I have read, it has changed the way it handles multiple result sets. I have used the WITH RESULT SETS to define the first result set but if I execute SSIS will fail indicating other result sets need to be defined.

简而言之，在 SSIS 2016 中复制 SSIS 2008 中的工作的最佳方法是什么?

In short, what is the best approach to duplicate what works in SSIS 2008 in SSIS 2016?

推荐答案

解决方案概览

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

Solution Overview

I made 2 Experiments on that issue, the first experiments showed that in case of stored procedures with no parameters, nothing changed in SQL Server 2016 and SSIS 2016, the first Result Set is returned and others are ignored.

第二个实验表明，在使用参数时，这会抛出异常，因此您必须使用WITH RESULT SETS选项定义元数据，然后删除此选项.

The second experiment showed that when using parameters, this will throw an exception, so you have to define metadata using WITH RESULT SETS option, then remove this option.

以下实验使用 SQL Server 2016 和 Visual Studio 2015 和 SSDT 2016

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

1. First i created this stored procedure

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 命令

使用以下命令

Then i added a Data flow task to SSIS package

Added an OLEDB Source, Recordset destination

In OLEDB source i select the Data access mode to SQL command

an use the following commnad

EXEC sp_Test

1. 单击列"选项卡时，它会显示第一个 ResultSet 结构

1. 我们成功地执行了它运行的包

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

I changed the stored procedures to the following:

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 命令:

And i used the following SQL Command in the OLEDB Source:

EXEC sp_Test ?,?,?

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

我正确地映射了参数.

运行包时抛出以下异常.

When running the package it throws the following exception.

[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 个结果集，并且该语句试图发送比这更多的结果集.".

[OLE DB Source 2] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.".

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

After that i tried to remove the With RESULT SETS option, so the command is :

EXEC sp_Test ?,?,?

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

I tried to execute the package again, so it is executed with no errors.

尝试使用WITH RESULT SETs选项来定义OLEDB Source metadata，定义完元数据后，去掉这个选项，运行包，这样就可以了只会成功获取第一个结果集.

Try to use the WITH RESULT SETs option to define the OLEDB Source metadata, after that the metadata is defined, just remove this option and run the package, so it will just take the first Result Set succesfully.

这篇关于使用多个结果集执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！