Foreach file enumerator issue in migrated SSIS package to ADF

Glasier 320 Reputation points

I've successfully migrated SSIS packages to Azure Data Factory (ADF) using a lift-and-shift approach. I set up both SSIS Integration Runtime (IR) and a self-hosted IR to connect to my on-premises server. I also enabled "ConnectByProxy" for all connection managers and Execute SQL tasks before migration.

The ADF pipeline runs the first package flawlessly, connecting to the on-premises server and executing stored procedures. However, the second package encounters an issue with a foreach loop that utilizes a "Foreach File Enumerator" to access files on a shared drive.

While the stored procedures in this package execute successfully, the foreach loop seems to be skipping or not functioning at all. Interestingly, if I remove the loop and keep the data flow task that directly loads a file from the same shared drive, it works perfectly.

My question is: Is there a specific setting for the foreach loop in ADF that needs to be enabled, similar to "ConnectByProxy" for connections? Or is there a configuration at the package level that I might be missing?

I'd appreciate any assistance in resolving this issue.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,456 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Smaran Thoomu 8,965 Reputation points Microsoft Vendor

    Hi @Glasier

    Thank you for posting query in Microsoft Q&A Platform.

    Based on your description, it seems that there may be an issue with the configuration of the Foreach Loop container in ADF. Please check the following settings:

    • Enumerator: Ensure that the enumerator is set to "Foreach File Enumerator" and that the appropriate folder path is specified.
    • Variable Mappings: Ensure that the variable mappings are set up correctly to map the file name to a variable that is used in the subsequent tasks.
    • Expressions: Ensure that any expressions used in the Foreach Loop container are set up correctly and that they reference the correct variables.

    Additionally, please ensure that the Self-Hosted Integration Runtime (IR) is set up correctly to access the shared drive. You can check the following settings:

    • Network Access: Ensure that the Self-Hosted IR has network access to the shared drive where the files are located.
    • Credentials: Ensure that the Self-Hosted IR is configured with the appropriate credentials to access the shared drive.
    • Firewall: Ensure that any firewalls or security groups are configured to allow access to the shared drive.

    If the above settings are correct and the Foreach Loop container is still not executing or skipping, you can try enabling debug logging in ADF to get more information about the issue. You can also try running the package locally on your development machine to see if the issue is specific to the ADF environment.

    If the issue persists, I encourage you to open a support case for further investigation.

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    0 comments No comments