locked
is possible use FOREACH LOOP CONTAINER doing a loop in a TABLE RRS feed

  • Question

  • Hello everybody,

    Anybody know if is possible use FOREACH LOOP CONTAINER doing a loop in a TABLE ?
    Anybody has a example?

    Thank you all
    Ricardo Nepomuceno Analista BI Sql Server
    Friday, March 5, 2010 7:09 PM

Answers

  • You need to change the type of @User::SK_PRODUTO to Object.
    Todd McDermid's Blog
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Friday, March 5, 2010 10:23 PM
  • Ricardo

    As Tod said use the variable type "Object" so do not use string or int32.
    Only the variable type object can be used.

    Regards,
    Ivo
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Monday, March 8, 2010 1:10 PM
  • Check out my blog for an example where i pull the data from table and use a foreach loop to iterate through each record.


    Hope this helps !!
    Sudeep Raj   |    My Blog |    SSIS Samples
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Monday, March 8, 2010 1:13 PM

All replies

  • You want to loop over all of the records in a table using a Foreach Loop Container?

    If so, retrieve the contents of the table into an Object-typed SSIS variable using an Execute SQL Task that returns the full result set.  Then use the ADO Enumerator on the Foreach Loop to loop over that Object variable.
    Todd McDermid's Blog
    • Proposed as answer by Ivo Lenting Monday, March 8, 2010 1:11 PM
    Friday, March 5, 2010 7:13 PM
  • Hello Todd,

    Exactly

    I created a variable SK_PRODUTO type String
    After I created a Execute SQL Task with a this select   "SELECT SK_PRODUTO FROM DIM_PRODUTO"
    In Result Set - Result Name is 0 and variable name SK_PRODUTO

    I created a Foreach Loop Container in colletion i puted Foreach ADO Enumerator
    I choose Rows in the first table in ADO OBJECT SOURCE VARIABLE = SK_PRODUTO

    When i execute this package is message wrong bellow

    [Execute SQL Task] Error: Executing the query "SELECT SK_PRODUTO FROM DIM_PRODUTO" failed with the following error: "The type of the value being assigned to variable "User::SK_PRODUTO" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Can you help me?

    Thanks,

    Ricardo
    Ricardo Nepomuceno Analista BI Sql Server
    Friday, March 5, 2010 8:03 PM
  • You need to change the type of @User::SK_PRODUTO to Object.
    Todd McDermid's Blog
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Friday, March 5, 2010 10:23 PM
  • Hello Todd,

    I chanced the type for int32 , the wrong message is the same. the Execute SQL Task is on the FOREACH LOOP CONTAINER or out ?

    i think that the FOREACH LOOP CONTAINER don't accept array

    [Execute SQL Task] Error: Executing the query "SELECT SK_PRODUTO FROM DIM_PRODUTO" failed with the following error: "The type of the value being assigned to variable "User::SK_PRODUTO" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Thank you,


    Ricardo Nepomuceno Analista BI Sql Server
    Monday, March 8, 2010 1:06 PM
  • Ricardo

    As Tod said use the variable type "Object" so do not use string or int32.
    Only the variable type object can be used.

    Regards,
    Ivo
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Monday, March 8, 2010 1:10 PM
  • Check out my blog for an example where i pull the data from table and use a foreach loop to iterate through each record.


    Hope this helps !!
    Sudeep Raj   |    My Blog |    SSIS Samples
    • Marked as answer by Tony Tang_YJ Friday, March 12, 2010 3:14 AM
    Monday, March 8, 2010 1:13 PM
  • Thank you Todd,Ivo and Sudeep,

     my package is ok now

    You're the guy.

    Best Regards,
    Ricardo Nepomuceno Analista BI Sql Server
    Tuesday, March 9, 2010 2:28 PM