Multiple data sets returned by proc

Question

Multiple data sets returned by proc

NicHopper

SSCrazy Eights

Points: 9090
More actions
January 15, 2019 at 8:57 am

#416630

Hi,
So I've a fun one to deal with. Picture this if you will;
You have 5 tables, each with 10 columns for simplicity we will call the tables A,B,C,D and E. and the columns on each will be 1-10, so A1,A2,A3 etc.
We are trying to ingest some data from a 3rd party but all they provide us with a stored procedure to call and it in turn returns the data. The catch is that the stored procedure returns multiple data sets, each with different information in it.
For example
Data set 1 returns the following columns - A1,A2,A6,B5,B7 and C1
Data set 2 returns the following - A4,A5,B1,B2.B6 and C7
Data set 3 returns the following - A10,C2,C4,C5 and B3
The challenge we have is how to get each source column the procedure returns into the correct table and column in our structure. As far as I know SSIS is not going deal with anything other than the first data set. So then I'm thinking of using a set of 'Landing procedures' which call the 3rd party procedures but just put the data into a landing table which matches it structure, from there I can then have 3 data flow tasks which go from the landing tables to our tables.
I just wondered if anyone had any other ideas on how to do this? I don't want to make it overly complicated or anything like that.
Many thanks,
Nic
Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

NicHopper - Tuesday, January 15, 2019 8:57 AM
Hi,
So I've a fun one to deal with. Picture this if you will;
You have 5 tables, each with 10 columns for simplicity we will call the tables A,B,C,D and E. and the columns on each will be 1-10, so A1,A2,A3 etc.
We are trying to ingest some data from a 3rd party but all they provide us with a stored procedure to call and it in turn returns the data. The catch is that the stored procedure returns multiple data sets, each with different information in it.
For example
Data set 1 returns the following columns - A1,A2,A6,B5,B7 and C1
Data set 2 returns the following - A4,A5,B1,B2.B6 and C7
Data set 3 returns the following - A10,C2,C4,C5 and B3
The challenge we have is how to get each source column the procedure returns into the correct table and column in our structure. As far as I know SSIS is not going deal with anything other than the first data set. So then I'm thinking of using a set of 'Landing procedures' which call the 3rd party procedures but just put the data into a landing table which matches it structure, from there I can then have 3 data flow tasks which go from the landing tables to our tables.
I just wondered if anyone had any other ideas on how to do this? I don't want to make it overly complicated or anything like that.
Many thanks,
Nic

You may be able to get this to work by using EXEC proc WITH RESULT SETS.
Check out some info here

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

Here is another link which may prove more useful.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Tim Mitchell SSCoach Points: 15685 More actions · Answer 3

Nick, I blogged about using the SSIS object variable to handle multiple result sets: https://www.timmitchell.net/post/2015/04/27/the-ssis-object-variable-and-m

I hope this helps!

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 4

Thank you both for the replies.

I best get reading your blog post Tim. I'll let you know how I get on.

Thanks again,

Nic

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/