Home > SSIS Best Practices > SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow

SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow

OK, let’s continue from the last blog SSIS – “Full result set” to a set variable of Object.

We have read a list of dates from a SQL query into a set variable vLoopSet.

Now we are ready to pass vLoopSet into a Foreach Loop Container.

Step 1: Create a Foreach Loop Container

In the Collection tab, pick Foreach ADO Enumerator as Enumerator. There are many choices for the Enumerator. I have not worked with types other than Foreach ADO Enumerator.

Then in the Enumerator configuration section, pick User::vLoopSet as the ADO object source variable, and rows in all the tables as Enumeration mode.

image

In the Variable Mappings tab, add a mapping by setting User::vLoopDate as Variable, and 0 (zero) as Index. Index 0 (zero) indicates that we want to use the first column from the result set.

image

vLoopDate is a previously created variable.

image

In the next blog, I’ll continue to show how to pass the date variable into a OLE DB Source in a Data Flow.

Categories: SSIS Best Practices

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

%d bloggers like this: