- Execute SQL Task – Get the records that need to be processed
- Foreach Loop Container – Loop through each record
- Execute SQL Task – Process one record at a time
Create two user variables:
- i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
- i.e. CollectionID (Int32) – the current ID to be processed
The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?
You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).
Now you want to store your Result Set into a user variable for looping through later.
In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.
We want to a user variable to store the current processed id.
You can specify a query or stored procedure to process each record.
i.e. [schema].[usp_ProcessRecord] NULL, ?
The above example has two input variables where one is always NULL and the ? represents an input parameter.
In this example, the input is a large integer.