Life is memorable. Share your experience. Help others. Return the favor. Terms Of Use Privacy Policy About FAQ Help Contact Us

As an Amazon Associate and affiliate with other third parties, Echofavor earn from qualifying purchases and display advertisements.

View Coupon

SSIS - Foreach Loop Container and Execute SQL Task

April 19, 2019
mcchu28
832 views

SSIS

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.png menus Gamecmms mm low hiatur mummy am mam Execute SQl Yask - . mm one man

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_21.png ﬁxmmm mu. X El Canflgme 0. plupeﬂis .q..i.d Inn 301 mnms and 50m puxadula using 0. s14d unmdiun. x 6mm Palamrlcl Mapping R50 3. mm. Tumom Cadrpag 1752 TypucamusiunMud R505 run mm 5.1 Sm Swanun Cannxﬁmﬁype Cannxfum muKeType mm 3....- mm... - nun... - r... SQueryStmedemduve Fake Dymllepale rm Ihn Swifts 0. mm 01 0. m. 3mg... Build 00... 2.3.5 Quuy 0K Camel

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

ssis_foreachloop_3.png mmmm mu. . Canflgule 0. plupeﬂis mum In Inn 301 menus and sImad puxadula using 0. gum mnmiun. x 6mm Valiabl Nam Dileum Data Type Palamrlcl .. Palamrlcl Palamrlcl Mapping - - .. mm pm men upmm Add Ram 0K Grid Hap

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

ssis_foreachloop_4.png mmmm mm. X Canflgule 0. plupeﬂis mum In Inn 301 menus and sImad puxadula using 0. gum mnmiun. 6mm Parameter Mapping Rank Nam Valiabl Nam Usu

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

ssis_foreachloop_5.png mm loop mm mm loop amsinu albns Km heraliun mu an numualiun. cm. mm... r..mma.....m mm Mappings mm mm Bum Swing 0. numualm lype. Enumlalmcunﬂgumﬁun ADO ubitd sunk mam Us gnummiun mud Ems 0. nm BM kg all 0. mm mooun mm any Allmbls ADO.NETd-mmumy ox Camel

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

ssis_foreachloop_6.png mm loop mg. Folexh mp mum. albus Wm ilemﬁun m. an numualiun. mm 13.th map 0. ullxiun yank. Valiabl hm Um

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.png mmmm mm. Canflgul 0. plupeﬂis Inquilad Inn 301 Shicmms and sImad pmdm using 0. gum mmm. Parameter Mapping Rank 31 apmim 5mm Nam mwlﬁrlvmnnzlm-d Dan pliun 2mm. 5m hdx 0min mom a Cadrpag 1752 TypdlmwusiunMud Alumni Sal RauhSri Ihn 5m swam Cnnmdimﬁype Canmiun mukeType Dim i...- mmmm sQueryStmedemdme Fake Dymllepal nu. Ihn Swing 0. mm 01 0. task. Bums Bid Canuy Eﬂ 13

In this example, the input is a large integer.

ssis_foreachloop_8.png mmmm m. . Canflgul 0. plupeﬂis Inquilad Inn 301 Shicmms and sImad pmdm using 0. gum mmm. ax 6mm Valiabl Nam 91min DataType Parameter Mapping Rams um... an ARGEJMIEGER apmim