SSIS Result set, Foreachloop and Variable
Asked Answered
E

1

8

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.

Exalted answered 17/12, 2013 at 19:33 Comment(4)
Please detail how you have your Execute SQL Task set up, the Variables you have declared and their data types and how you have the ForEach loop configuredWestwardly
SQL is OLE DB with "Full Result set", Result Name 0, 1 mapped to two variable, User::email and user::Fullname. Both are "Object" data type. Foreach loop is set to ADO Enumerator, Source variable set to User::email. but is failed on the SQL task when i add multiple columnExalted
What is the error you get? When I created similar query and assigned to two variables in result set, I got error in Execute SQL Task itself.Glint
as soon I add the second variable the SQL task fail with no errors.Exalted
E
12

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.

Exalted answered 18/12, 2013 at 16:10 Comment(2)
You only need one Object variable for your SQL task result set. Then you just that as your ADO Object Source Variable and map each of your resultset columns by index to a variable (email_St, name_St), etc.Stripling
Error: The type of the value being assigned to variable "User::BaselineId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.Carpetbag

© 2022 - 2024 — McMap. All rights reserved.