1

I have a Stored Procedure: myProcedure returning 2 different resultset in the end like:

select * from #alldata where <condition1>

and 

select * from #allData where <condition2>

Please note that I am not allowed to modify the SP.

What I need is to get the second (last) result set returned from the SP and save it in a temp table in SSIS 2012.

I managed to do is by using a script task including the line:

DataSet ds = db.ExecStoredProcedureDataSet("[myProcedure]", sqlFilters).Tables[1];

I wonder if there is a way to handle it by using "Execute SQL Task" instead.

When I check the topic below, it seems it would be possible if the SP returned one resultset only, but couldn't find a way in my situation where the SP returns multiple resultset and I need the last one saved in a temp table only. Any help would be appreciated.

Insert results of a stored procedure into a temporary table

Edit: It is not duplicate of the indicated topic, I need a solution that would work in Execute SQL Task process in the Control flow of SSIS.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • Possible duplicate of [Retrieve data from stored procedure which has multiple result sets](https://stackoverflow.com/questions/20082889/retrieve-data-from-stored-procedure-which-has-multiple-result-sets) – Bacon Bits Oct 30 '18 at 15:56
  • As far as I'm aware, there is no way to capture output from a stored procedure with multiple result sets using just T-SQL. You may be able to fake it using a CLR stored procedure, or you may be able to rewrite the stored procedure to only produce one result set with certain input, but if neither of those is available you're stuck. – Bacon Bits Oct 30 '18 at 15:57
  • It is not duplicate of the indicated topic, I need a solution that would work in Execute SQL Task process in the Control flow of SSIS. – Eray Balkanli Oct 30 '18 at 15:58

1 Answers1

2

From the docs:

If the Execute SQL task uses the Full result set result set and the query returns multiple rowsets, the task returns only the first rowset. If this rowset generates an error, the task reports the error. If other rowsets generate errors, the task does not report them.

So, SSIS Execute SQL Task cannot access multiple result sets from a single proc. Only the first can be accessed.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52