SSIS Result set, Foreachloop and Variable

Question

I'm having an issue and I don't know if this is possible to do.

I have a simple SQL task.

SELECT Name, email FROM table_name

Using a result set, I'm trying to pass it to a variable and use it on a email task with a foreach loop but i having no luck getting this to work. If I do just one column, it works fine. My issue is multiple columns.

Thanks for the help in advance.

Answer 1

I figured out my own question. I will add it here in case someone else have the same issue.

1. On the SQL query the first column should be your key result. for example mine was the email.

2. In the SQL Task under General, set Resultset to "Full Result rest"

3. In the SQL Task under Result Set, set variable with "0" as result name, create a variable as "Object" data type for the email column and click ok to save and exit. Example: email_Ob

4. By going to the variable window, create more variable for the other columns as Object and since I'm using this for email task I need to convert from Object to String, so I need to create another variable as string for each one

   Example:

          **Variable Name**       **Datatype**
              email_Ob                Object
              Name_Ob                 Object
              email_St                String
              Name_St                 String
   

5. Foreach Loop task Under Collection change the following:

   Enumerator: Foreach ADO Enomator

   ADO object Source variable: Select the key variable (email_Ob)

   Select Rows in the first table

6. Foreach Loop task under Variable Mappings add the variable with String data type the same order as on your SQL query. Click OK to save and exit.

7. Now you can add the Email Task inside Foreach Loop and use those String variable as part of the email or you can use it for any other task.

Hope this help and if you have any questions feel free to ask. Doing this way, I was able to add more columns as need it.