SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS and looping through result set


SSIS and looping through result set

Author
Message
gunning
gunning
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17654 Visits: 7427
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”
gunning
gunning
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search