SSIS - Building and Sending Email based on SQL Table Values?

[batcater98]

have a table in SQL that gets updated inside another SSIS package. I am trying to figure out a way to be albe within an SSIS package to parse through the table which contains a flag field of "yes" or "no", other data and a field with an email address in it. I want to parse through the data and where I find the flag field to be yes send an email to the email address in the email field with data from other fields in the record.

As anyone done this and if so - do you a step by step plan to show me the way? I looked at the Send Mail Task, but it appears to not be flexable enough to do what I want.

Thanks,
Ad.

 

[MDXer]

You need to do this utilizing a For Each loop

1) Create a Variable of type object (EmailInfo probably a Good Name.

Execute SQL task to execute the query. ResultSet needs to be FullResultSet. on the resultset tab assign Result Set name set to 0 and Variable Name set to the variable you created.

3) Create variables for each portin of the email that you will feed in from the query of type String (To, Message, Subject)

4) Add your for each loop and use the EmailInfo Object Variable as your source for the loop. Configure the task to populate the other variables.

5) Add your Email task within the loop. In the properties edit the expressions and Create an expression for each item to be populated from the variable.


Untested but it should work.

 

[batcater98]

Thanks for the help - I am still not clear on where and how you assign assign the data from the SQL Task Pull into the variables?

Here is what I have done - I built and SQL Task to pull in all the data I need and set it to a FullResultSet and setup the ResultSet Name as 0 and assigned it to the variable with type of object.

The SQL task is outside the For Loop - I link the SQL Task to the For Loop and under collection on the for loop I pick ForEach From Variable Enumerator and pick the Object Variable from the SQL Task.

From there I am not sure how to assign what column from the SQL pull to what variable so it can be used in the Send Mail. Also - when I setup an override in sendmail for the ToLine - leaving the To blank under Mail causes and error.

Can you give me a little more guidance?

Thanks,
Ad.

 

[MDXer]

you need to have some value in the to line even if it is your own as this will be over written. I will have to look at the For Each loop container and see where it is as I don't remember off the top of my head.

 

[batcater98]

I got it - Thanks for all the help!!!

Ad.