SSIS ForEach Loop Container only processing one row

Question

I have a simple job that grabs Employee IDs and sends them to an API call, which only accepts one Employee ID at a time. I have this setup using a ForEach Loop Container being fed by an Execute SQL Task.

The job runs as expected, but it is only looping through the top N record.

My Execute SQL Task is returning a full result set to a variable. My variable is set as an Object data type.

I have the ForEach Loop set to Foreach ADO Enumerator, then matched to my variable.

When I run the job I don't get a specific error message telling me what I'm doing wrong, but it fails at the ForEach Loop as shown

Anything else I can look out for?

Answer 1

The Execute SQL Task needs to shove results into an SSIS variable of Object type. I assume associateOID is a string type, yeah?

What you need to do is create an SSIS variable called something like rsEmployees of type Object. Your ResultSet in Execute SQL Task will then map to User::rsEmployees

Change the Foreach Loop ADO enumerator to use the variable User::rsEmployees

At this point, the package will execute and store the results into our rsEmployees variable and then the Foreach enumerator will iterate through the records in our set. The last piece is to do something with the current row. That's where your User::associateOID comes into play.

In the Variable Mappings tab of the Foreach Enumerator, add User::associateOID to ordinal 0. That will provide the linkage between the "current row" that is being shredded in the enumerator and our SSIS variables