Passing data from db to ssis variable

Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?

Thanks,

Execute SQL task can do this.|||I tried, but don't even know where to begin. is there a tutorial or something on the net some where?|||Have you searched this forum? Tons of examples.

Execute SQL task.

If you are selecting where the result is a single row:
Result Set - Single row. Result set tab: Result Name: 0 thru n based on the number of columns. Then simply select the variable name you need to map to.|||There is more than 1 row in my table and 4 columns. I looked at a couple of examples but can't figure it out.|||http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

|||

mr4100 wrote:

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

Sure you can. What do you mean nothing happens?|||

sorry to keep bugging you and I appreciate your help and patience, but when I open my package the send mail task has error code can't find recipient. I have 4 expressions in this mail task:

toline--set to my to variable

fromline--set to my from variable

subject--set to my subject variable

messagesource--set to my body variable

From what i gather, it's not getting the data from the variables for some reason. Everything seems to look ok.

|||Perhaps you need to add a script task inside the foreach loop so that you can display (via "MsgBox()") the contents of the variables to actually verify that they have data in them.|||i tried that but i get an element is not found in the collection error.|||

mr4100 wrote:

i tried that but i get an element is not found in the collection error.

First, make sure that your variables are of package scope, not some other task scope.

And how did you reference the variables in your script task?|||

they are part of the package and not just a specific task, i also set the delay validation property to true for the foreach loop and mail task, and the error is cannot recognize recipient address. Here is my script code.

Public Sub Main()

Dim Message As String

Message = Dts.Variables("to").Value + " " + Dts.Variables("from").Value + ", " + Dts.Variables("subject").Value

MsgBox(Message)

Dts.TaskResult = Dts.Results.Success

End Sub

|||Try this instead:

Code Snippet

Public Sub Main()
Dim Message as String
Dim vars as Variables
Dts.VariableDispenser.LockForRead("to")
Dts.VariableDispenser.LockForRead("from")
Dts.VariableDispenser.LockForRead("subject")
Dim ToVar as String = vars("to").Value.ToString
Dim FromVar as String = vars("from").Value.ToString
Dim SubjectVar as String = vars("subject").Value.ToString

Message = ToVar + " " + FromVar + ", " + SubjectVar
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success
End Sub

|||well, i get an error with this code as well something about object reference not set to an instance of object