SSIS - Execute Stored Procedure with multiple result sets

Problem:

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.

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 = ?;

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.

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.

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

Solution:

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.

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.

---

Details

Experiment 1

The following experiment are made using SQL Server 2016 and Visual Studio 2015 with SSDT 2016

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

2. Then i added a Data flow task to SSIS package
3. Added an OLEDB Source, Recordset destination
4. In OLEDB source i select the Data access mode to SQL command
5. an use the following commnad

   EXEC sp_Test

6. When clicking on Columns Tab it shows the first ResultSet structure

7. And we i executed the package it runs succesfully

Experiment 2

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

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)
)
)

And i mapped the parameters correctly.

When running the package it throws the following exception.

[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.".

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.

Conclusion

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.

Original post: https://stackoverflow.com/questions/48083262/execute-stored-procedure-with-multiple-result-sets/48084153#48084153