CODEWITHSUNDEEP
sqlsql-serverssisssis-2012
Eray Balkanli
Eray Balkanli

Reputation: 7960

How to save the last result set of SP which is returning multiple result sets into a temp table in SSIS?

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.

Upvotes: 1

Views: 618

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 2

Related Questions