SSIS – Foreach Loop Container and Execute SQL Task

Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
  • 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
ssis_foreachloop_1

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 = ?

ssis_foreachloop_2

You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).

ssis_foreachloop_3

Now you want to store your Result Set into a user variable for looping through later.

ssis_foreachloop_4

In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.

ssis_foreachloop_5

We want to a user variable to store the current processed id.

ssis_foreachloop_6

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.

ssis_foreachloop_7

In this example, the input is a large integer.

ssis_foreachloop_8

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s