Welcome to PC Teach Me...

Free Computer Training Videos.

Although you do not need to register to access this site. Registering has its benefits. Such as:

  • Access to training material associated with the video you are watching [if applicable]
  • Automatic email updates when new training videos are available.
  • Ask for a training video.
  • Exclusive access to pose questions to me [limited availability as I am doing this for free :-)].
  • Free contribution of your own training topics, this includes referencing your videos outside of PC Teach Me.
  • Its Free!

Member Login

Lost your password?

Not a member yet? Sign Up!

By registering with this blog you are also agreeing to receive email notifications for new posts but you can unsubscribe at anytime.

25425 Registered Users
71 Number Of Articles
Free Video Tutorials (Email) Free Video Tutorials (RSS) Free Video Tutorials (Twitter) Free Video Tutorials (Flickr)

SSIS: Foreach Loop

ssis SSIS: Foreach LoopThe Foreach Loop


You will get to a point when uploading data to a database can get repetitive.  The same structure of files needs to be imported over and over again.  Instead of creating multiple data flow tasks you could look performing a loop.  Specifically a foreach loop.

How does the foreach loop work?

Well, if you have never done one before they can be quite challenging.  Why? Because Integration Services isn’t the best when it comes syntax checking and error trapping your own darn code!  Sometimes you need to do a quick Google to get you out of what seems a labyrinth of obstacles. So how does the foreach loop work then?

It uses several components that are:

1. The foreach loop container.
2. One or more variables.
3. Probably some script tasks (if dealing with external files which I am 90% sure you’ll have!).
4. Knowledge of how connection strings work.

Basically, the foreach (file enumerator) loop looks at a specific folder and counts the amount of files which are in there and then goes through each file one at a time.  But is not as simple as that, as you will need to tell SSIS what to do with each of the files.  This is where the variable comes in as each file in turn will be assigned to the variable which you can then use within the dataflow to instruct SSIS how to deal with it.

In this example the foreach loop will iterate through each Excel file and then concatenate the variable to a connection string to re-point the connection manager to the next file, then the next file and so on.  This means that each time the foreach loop picks up the next file the same dataflow can be used.

Now depending on your needs that may be enough, however, if you are using external files such as CSV or Excel you may need to perform a check prior to running the foreach loop to ensure the files are present.  As you have probably already experienced, you need to tell SSIS implicitly how to perform anything so much a minor issue can cause the entire package to fail so it is recommended that you error trap prior to performing the foreach loop.

So, this video will show you how to create a foreach loop and be able to mass insert the same structured type of data over multiple files in one swoop.  On a caffeine rating, I’d give this one a 7 cupper (followed by headache tablets!)

Don’t get me wrong this is not the worst thing out there but if this is new territory it is one of those learning curves you need to go through to become the ETL star that you are!

This video is one of many SSIS tutorials on this site click here for more.

The foreach example file

To obtain the sample files please click here:

Related Posts

7 Comments

  1. DevITIS says
    09 Jun 14 at 3:22pm

    Well done indeed. Just one minor oversight in the CSV results file, the
    “lastNameFirst” column will not parse correctly because it contains a
    comma. Cheers! :)

  2. Akash Mitra says
    15 Sep 12 at 1:08am

    Absolutely brilliant explanation. Well done.

  3. dmedici703 says
    04 May 12 at 5:04am

    Great vid. One question though – my destination is showing multiple rows with the column names, whereas yours shows only one at the top. Is there a way around this? I am using flat files for both my sources and destination

  4. dmedici703 says
    04 May 12 at 5:04am

    [..YouTube..] Great vid. One question though – my destination is showing multiple rows with the column names, whereas yours shows only one at the top. Is there a way around this? I am using flat files for both my sources and destination

  5. WatchOut4Keith says
    25 Feb 12 at 2:08pm

    Great video! Clear cut and gets down to the meat of the subject.

  6. 25 Feb 12 at 2:08pm

    [..YouTube..] Great video! Clear cut and gets down to the meat of the subject.

  7. svk says
    15 Dec 11 at 11:10pm

    Thanks! great video. One quick question, why not use the “fully qualified” option in the “retrieve file name” section instead of adding another variable just for the folder path (and in the Expressions)?

Leave a Comment

You must be logged in to post a comment.

Valid XHTML 1.0 Transitional website security