December 23, 2015 at 10:52 am
I'm working on an SSIS pkg. that I need to run a stored procedure to return a pretty large results set. I'm assuming this will need to be done via an Execute SQL Task object. After I get this initial large result set, I'm trying to figure out how to turn around and loop through the result set, passing-in a unique "Company Ident" throughout each loop to query the results of the initial stored procedure results set, Company Ident by Company Ident to do something with these queried results each time throughout the loop.
Currently, this stored procedure that returns the large results set is somewhat rerunning each time throughout the current loop, and it's a performance hog. I'm trying to figure out a way to run this initial/large stored procedure ONE TIME, hold all of the results in memory in a result set variable, and THEN loop through these results using (example) Company Ident "ABC", "XYZ", "GHI", etc. and do something with those results.
Conceptually, I understand what I'm trying to do, but I'm having difficulty figuring out how it can be implemented within SSIS.
Any suggestions or pointers in a helpful direction or a helpful link where someone else has done this sort of thing are greatly appreciated.
Thanks!
December 23, 2015 at 11:39 am
brad.mccollum (12/23/2015)
I'm working on an SSIS pkg. that I need to run a stored procedure to return a pretty large results set. I'm assuming this will need to be done via an Execute SQL Task object. After I get this initial large result set, I'm trying to figure out how to turn around and loop through the result set, passing-in a unique "Company Ident" throughout each loop to query the results of the initial stored procedure results set, Company Ident by Company Ident to do something with these queried results each time throughout the loop.Currently, this stored procedure that returns the large results set is somewhat rerunning each time throughout the current loop, and it's a performance hog. I'm trying to figure out a way to run this initial/large stored procedure ONE TIME, hold all of the results in memory in a result set variable, and THEN loop through these results using (example) Company Ident "ABC", "XYZ", "GHI", etc. and do something with those results.
Conceptually, I understand what I'm trying to do, but I'm having difficulty figuring out how it can be implemented within SSIS.
Any suggestions or pointers in a helpful direction or a helpful link where someone else has done this sort of thing are greatly appreciated.
Thanks!
It's not clear how complex your second level of processing is.
I'm assuming that you can't use the stored proc as a data flow source and send that through a Script Component transformation to do your 'additional' processing? If not, can you elaborate a little on why?
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
December 28, 2015 at 7:40 am
The initial stored procedure uses several Views and Functions that are complex and time-consuming to run. This stored procedure as it stands at present runs approximately 500 times (once per company that it needs to retrieve data for). What I'd like to do is run this stored procedure one time and retrieve data for ALL companies. Then, I'd like to be able to cycle through or query that recordset once for each company and export records for each company to Excel files. I know how to do the file exports as this is working right now. In fact, everything is working but I'm just trying to get around running this one complicated SP 500 times and just run it once and query its data as I outlined above.
Thank you for any additional recommendations.
December 28, 2015 at 7:52 am
Ok thanks. Have you considered creating an appropriately indexed 'working' table to hold the results?
I'd suggest including a date created column.
Then your package logic becomes
1. Should I recreate results? (Based on date created?)
If yes,
a) Truncate work table
b) Run proc to repopulate work table
2. Use work table results / for each loop for all further processing.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply