January 15, 2019 at 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
January 15, 2019 at 9:04 am
NicHopper - Tuesday, January 15, 2019 8:57 AMHi,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 B3The 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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 15, 2019 at 9:07 am
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
January 15, 2019 at 9:13 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply