Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS and looping through result set Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:24 AM
Points: 9, Visits: 26
Hi all,

Ive seen a few variations/pointers on this but nobody doing what I am trying. I *think* this is the best way to do it, just not sure if its possible tbh! What I want to do is execute a stored proc (no probs) which basically does a bunch of lookups and calcs and then returns a dataset to the SSIS where each row contains information that I want to email using the "send mail task" to email addresses that are in each row of the result set.
So I guess my question is...is it possible to use the for (or foreach) loop container to grab each row of the result set and churn through them setting a variable to the email address and sending out the email ? If it is does anyone know of any examples, doesnt really matter how tenuous they are I should be able to muddle through, I just wondered if I was on to a hiding to nowhere with this plan.
Thanks
t
Post #1392376
Posted Tuesday, December 4, 2012 5:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 3,866, Visits: 5,017
Create a variable (at project scope) of type "object"
Create a data flow, executing the stored procedure as it's source, with a Record Set as destination, wherein you instruct the results of the stored procedure to be inserted into the above created variable.
Then create a for each loop over the variable, extracting the relevant columns and allocating to your variables for use in the e-mail task.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1392386
Posted Tuesday, December 4, 2012 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:24 AM
Points: 9, Visits: 26
hi thanks for that, I've also just found this... [url=http://www.sqlis.com/sqlis/post/Shredding-a-Recordset.aspx][/url] (I did see it earlier when searching but didnt follow it due to the shredding part....curiosity got the better of me second time around. And Im glad it did)
Post #1392392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse