0

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. Execute SQL Task

Execute SQL Task Full Result Set Variable

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

Foreach Loop Editor

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 ForEachLoop

Anything else I can look out for?

jdids
  • 561
  • 1
  • 7
  • 22

1 Answers1

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I updated my question. I am using the Object data type for [User::assocaiteOID]. Looks like I'm doing everything you suggested but only using one variable. When I add in the second variable, the package will not execute anymore. – jdids Dec 03 '20 at 19:09
  • 1
    You must use two variables. `Object` is the only thing that can handle more than one result from the Execute SQL Task and you need a type-specific variable to be used in any other bit of the SSIS componentry. – billinkc Dec 03 '20 at 19:33
  • I completely misunderstood you. Looks like we're in business now! I had [User::associateOID] set as an object. I changed that to String and followed your directions again and now we have data! Thank you for your help! – jdids Dec 03 '20 at 21:27