none
Can I loop through a SQL table using the Foreach Item Enumerator? RRS feed

  • Question

  • I need to loop through records in a table and process them (I have the process part figured out).  I initially tried using the Foreach ADO Enumerator, but I'm having issues (errors) with the field variables.  I believe using the Foreach Item Enumerator would be a simpler and better maintenance-wise approach, but I'm not able to find anything related to the Foreach Item and recordset looping.  Is this possible?

    Rambler_358

    Wednesday, May 15, 2019 4:49 PM

All replies

  • Hi William - Thank you for the response. However I didn't see anything in the links you provided related to my question.

    Rambler_358

    Wednesday, May 15, 2019 5:05 PM
  • Hi - Yes, specifically using the Foreach Item Enumerator in SSIS to loop through records in a SQL table.

    Rambler_358

    Wednesday, May 15, 2019 5:24 PM
  • Hi Rambler_358, 

    If you need to extract data from an Object variable row by row, Foreach ADO Enumerator will be the best choice. 

    Please refer to this link, if you have any trouble with it, please post the error messages and images of the settings in your package:

    Working with “Foreach Loop container” with “Execute SQL Task”


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Yang.Z Tuesday, May 21, 2019 2:23 AM
    Thursday, May 16, 2019 6:22 AM
  • Hello Yang - So it's not possible to use the Foreach Item Enumerator to loop through a SQL table?

    I'd prefer this route, but if it's not possible then I'll continue to try the Foreach ADO Enumerator and post back.


    Rambler_358

    Thursday, May 16, 2019 3:01 PM
  • Hello, 

    No, it's not possible to use Item Iterator to iterate through an Object variable. 

    Here is a scenario where Item Iterator can be useful:

    Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 17, 2019 1:28 AM
  • Hi Yang - Sorry for the late response, I've been tasked with a higher priority project for the moment.  I know I couldn't use an Object variable with the Item enumerator, that's why I was looking for an alternative.  The link you posted above may work, but uses a flat file destination which I won't be doing.  When I get back on the project I'll go through the link again and see if it'll work for my needs.  I'll post back once I give that a try.

    Rambler_358

    Tuesday, May 21, 2019 2:56 PM
  • Hi Rambler_358, 

    Thanks for update. 

    If you have any trouble during the test, feel free to ask. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 22, 2019 2:26 AM
  • Hello, 

    No, it's not possible to use Item Iterator to iterate through an Object variable. 

    Here is a scenario where Item Iterator can be useful:

    Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection


    I'm back to trying this again for a short bit.

    The above link seems like it might work for me, but it's not exactly what I need.  I'd like to use the Foreach Item Enumerator to loop through the rows in a table and store the field contents that will be sent to a web process (I'm using the KingswaySoft SSIS Productivity Pack, and their Web Service Destination component) in the Data Flow task.

    The above link iterates through a list of servers and databases, I'd like to iterate through the records in a table in the same manor if possible and pass the column field contents to the web service component.  I'm using a SQL command in the Data access mode of the OLE DB Editor to select the fields from the table I'm using.  But when I run the package, it stops right before the Data Flow task with no errors.

    So I'm not sure if the Foreach Item Enumerator scenario will work for me like it does in the website example?


    Rambler_358

    Monday, June 10, 2019 5:52 PM