Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Enumerating files in a Foreach loop

I've just got back from a Charlatans gig at Hammersmith Palais in London. Anyone that knows me will tell you that I'm a massive massive Charlatans fan so I've had a great night. They played alot of stuff from earlier albums like "With No Shoes" from Tellin' Stories, "Inside Looking Out" from Up To Our Hips and "Here Comes A Soul Saver" from the eponymous fourth album; songs that I never thought I'd ever hear live again so after the gig I was buzzing.

But, even given all that, I'm down in the dumps cos soon after I realised that some b*****d tea-leafed my Pentax Optio S5i from right outta my pocket. I've only had it a few months and had a summer of music festivals, camping trips and lazy days in the sun planned in which to really get some use out of it - not any more. The missus has been sympathetic but I don't think she truly understands the pain a gadget freak such as myself goes through in these situations.

Anyway, it means that I'm wide awake contemplating the ills of the world so plenty o'time to do some much needed blogging before Mark overtakes me in the Conchango blogging leaderboard :)

 

I've recently been in touch with Richard Lees, a kiwi living in Sydney, who has been making his first steps on the treacherous road to SSIS enlightenment. Richard was having a bit of grief understanding how to use the Foreach loop container to loop over a load of files in a folder so I put together this simple downloadable demo to illustrate the concept.

To use the package you need to place the 3 enclosed text files into your "C:\Temp" folder and make sure there are no other "*.txt" files in there. Then simply run the package. It does a number of things:

  1. Enumerates all the files in "C:\Temp" matching the wildcarded string "*.txt"

  2. Maps the file path of the currently enumerated file to a variable User::FileName

  3. Uses a property expression on the ConnectionString property of a Flat File Connection Manager to point to the file indicated by User::FileName

  4. Process the file (which in this demo simply passes all the data into a Recordset destination)

I hope this alleviates some of the pain people are having in using the file enumerator in the Foreach container. Its a wonderfully powerful feature of SSIS and will have many uses as SSIS gets unleashed unto the masses.

Meantime, I am going to leave you, stick on my most downbeat Radiohead CD and return to my morose mourning for my beloved camera!!!

-Jamie

 

Published Monday, May 30, 2005 12:59 AM by jamie.thomson

Comments

 

jamie.thomson said:

Thanks for this nice example.

While following your guide to get it working I got a bit stuck at the end where the Recordset Destenation needed the variable to store its results in.

The error SSIS gave wasnt descriptive but I learned from your package how to solve it.

For the rest it went very smooth.
I am looking forward to making great things with this technology.

I did some testing afterwards with the web service task trying to connect to the Amazon Web service.
No luck yet since I dont understand a thing about WS's for now. Maybe a hint for a next blog post? ;)
May 30, 2005 11:47 PM
 

jamie.thomson said:

Hi Tom,
I've been planning to do something around Web Services since back in February. The reason I haven't is because I wanted to use it to also demo some of Conchango's web services offering.
We've an initiative underway to set up a dedicated web services portal here at COnchango so I'm waiting until that is done.

I've been waiting since February though so don't hold your breath!!!

Thanks very much for the comment. Always appreciated believe me.

-Jamie
May 31, 2005 7:08 PM
 

Jason L. said:

Jamie,

If I am using a foreach container with a file enumerator, is there any way to change the folder which is being enumerated at run time using a variable? None of the enumerator properties are available to set as expressions.

Thanks.

P.S. I had the other questions about dataflow configuration...thanks for answering it.

jasonlodice_at_gmail_dot_com
August 9, 2005 2:25 PM
 

Donny said:

Very interesting blog!
September 17, 2005 5:10 AM
 

SSIS Junkie said:

Quite often you may want to process data from multiple files all at the same time. There are a number

October 14, 2006 6:26 PM
 

Uja said:

Hi,

I went through this Blog,"Processing data from multiple files all at once " I tried to with option 2 - "Loop over the files in a Foreach loop".

I did Set up all the this as per the document here.

But my for eachloop continer loades same file for many times. (e.g. if have 5 files in the folder it loads same file 5 times.) It does not points to the next file.

Please let me know how to set it.

Appreciate your help.

Thanks,

Uja

October 27, 2006 4:38 PM
 

Eric D. Burdo said:

I know this is a REALLY late answer to what Jason asked... but I am having the same problem.  I want to use a variable for the folder where the files are contained.

After much searching, I finally found this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=21839&SiteID=1

The post by Marianne looks like it should let me set the path to what I want.

January 17, 2007 5:36 PM
 

Pieter O said:

Ok, I'm not sure wether I missed the part where you explained how to use a recordset destination in a FOREACH , but I can't figure out how to open a Recordset Object Variable in a script task within a FOREACH loop

February 6, 2007 9:35 AM
 

jamie.thomson said:

Pieter,

I'm a bit confused, what exactly are you trying to achieve when you say that?

-Jamie

February 6, 2007 2:47 PM
 

SmokedIronMade said:

This is nice and have help me, but there is something missing that someone asked before and i cannot see an answer, i will ask again maybe i can explain it better, i require to process multiple files in a folder, folder and mask for file name will come from variables loaded from database, so what i don't know how to do is to tell to foreach that the folder and file mask must be taken from a variable, i guess it can be done using expression properties, but don't know which ones.

Thanks.

March 30, 2007 5:25 PM
 

jamie.thomson said:

March 30, 2007 7:32 PM
 

SmokedIronMade said:

You can dismiss my question, I already found the answer, the problem was that the Properties you can specify in the Expressions that are shown in the Properties window are not the same that the ones shown if you click in the Expressions inside Edition dialog of Foreach. This is some kind weird.

-SmokedIronMade

March 30, 2007 7:34 PM
 

jamie.thomson said:

Why is it weird? The Task Property window shows you properties of the task. The Enumerator has proeprties all of its own. They're not the same thing.

-Jamie

March 30, 2007 7:39 PM
 

SmokedIronMade said:

Yeah, your answer was just right, but i was able to find it by myself just a minute before you published your answer. Thanks anyway.

-SmokedIronMade

March 30, 2007 7:40 PM
 

PKDenver said:

Is there a way to dynamically choose the most recent file in a directory using an expression on the connection string of a flat file source?  We receive a file download from a customer on a daily basis.  The file always begins with down_%%%%%%.csv.  The percent signs are dynamic.  We need to hang on to the history so I don't want to delete the files.  I could move them to a archive directory but thought it would be nice to just select the newest file.  Any directions I could investigate?

May 10, 2007 5:15 PM
 

jamie.thomson said:

PKDenver,

No, there's no way to do this using an expression. You'll probably have to do something funky with a script task to get the most recent file.

-Jamie

May 10, 2007 5:48 PM
 

Calvin Brown said:

Got a question on the ForEach Loop container.  I am trying to loop over a set of .mdb files and import them into a SQL 2005 database.  If in the course of uploading the file an error occurs (such as an incorrect structured .mdb file), I want to move the offending file to a "bad file" folder and continue processing the next file in line.

I am able to successfully have the file moved to the "bad file" folder, however the package dies with an error and it does not move to the next file.  If I execute the package again, the remaining files are successfully processed.

How can I configur the package to continue processing the remaing files without abending?

Thanks for your help,

Calvin

September 4, 2007 10:34 PM
 

jamie.thomson said:

Calvin,

Try changing FailParentOnFailure property.

-Jamie

September 4, 2007 10:39 PM
 

Calvin Brown said:

Jamie,

Thanks for the quick response.  I already have both FailParentOnFailure and FailPackageOnFailure set to False, however the package still fails.  Any ideas?

Thanks,

Calvin

September 5, 2007 4:56 AM
 

Matt Hunt said:

Anyone,

I'm in a similar boat, please may I explain.

I have a Foreach Loop that loops through a set of files. The first thing I do is check the header of the file using a Script Task. If the header is wrong I throw and catch an exception and use Dts.TaskResult = Dts.Results.Failure in my catch block. This fails the entire Foreach Loop does not process the rest of my files.

I would like to know if there is a way you can move to the next file pragmatically in a Script Task.

September 12, 2007 11:59 AM
 

Ian said:

This may have already been discovered but if you want the loop to continue, even if a task within the loop fails then set the MaximumErrorCount to be, say, 100 in the ForEach Loop

October 10, 2007 3:32 PM
 

Peager said:

I can see how this works with flat files (TXT) but how do you make it work with XLS files.  I really need to enumerate through a set of XLS files in a directory, open them, and import data from them.

Paul

October 31, 2007 10:05 PM
 

FCoen said:

Hi,

This is a very interesting blog!

Is there anyway to read the last modified date of a file in a folder using SSIS?

Fiona

November 9, 2007 9:46 AM
 

jamie.thomson said:

Hi Finoa,

There is no task that will do it. I've asked for an enhancement to the FileSystem Task that will allow it but its not going to happen any time soon.

You'll have to resort to the script task.

-Jamie

November 9, 2007 1:00 PM
 

addrockride said:

anyone know how to get this to work for excel. i've tried using the related ms article...

http://msdn2.microsoft.com/en-us/library/ms345182.aspx

, but ofcoarse it doesn't work :(

December 3, 2007 9:27 PM
 

7 said:

tried that article too ,didnt work for me :-(

January 29, 2008 7:51 PM
 

Gary Wilkinson said:

I've got a question regarding this bloody container.  I have created a simple SSIS package that uses the foreach loop.  I have another .NET application which runs the SSIS package programmatically.  If I run the package within the application thread, the foreach loop does not work.  To get it to work, I have to start a new thread and then run the package.  This took me a few hours to figure out and I'm now bald.  Have you come across this at all?

February 28, 2008 2:00 PM
 

jamie.thomson said:

Hi Gary,

No, that's news to me. I'll try and investigate.

-Jamie

February 28, 2008 2:07 PM
 

Matt Masson said:

I don't think I've seen this behavior before, but I've seen strange things happen if the .NET application is run STA instead of MTA. I have a blog post about it here:

http://blogs.msdn.com/mattm/archive/2007/09/14/running-packages-from-custom-applications.aspx

If the app is STA, which I believe is the default for .NET apps, then try switching it to MTAThread.

February 28, 2008 3:04 PM
 

Gary Wilkinson said:

Matt Masson, a.ka. genius, you are now officially my hero.  If only I found this out this morning, it would have saved a lot of headbanging and cursing.  I even invented some new swear words!!  Cheers mate, much appreciated.  

February 28, 2008 4:18 PM
 

Matthew Roche said:

Dare I mention that no one would EVER steal your camera at a Manowar festival?

March 11, 2008 10:33 PM
 

Josef Karmona said:

Hi there,

I've added your package but I'm struggling with the Property Expressions Editor.

Although the package runs ok, when I go to the Foreach Loop Container's Expressions, there's no ConnectionString available there (only DelayValidation, Description, Disable, etc)

What am I doing wrong????

April 23, 2008 2:55 PM
 

jamie.thomson said:

Hello Josef,

In the example above the ConnectionString property is a property of the Flat File Connection Manager.

Regards

Jamie

April 23, 2008 3:13 PM
 

Geoff said:

Thanks a bunch. Did you learn this from somewhere in particular? I'm finding I'm learning SSIS through trial and error and from other people. I'm wondering where to go to a) get good documentation on the tool b) fully exploit the capabilities of SSIS.

May 30, 2008 3:15 PM
 

jbanko said:

This is a real noobie question but if I'm putting the path/filename into a variable:

User::Filename

And using an Execute SQL task inside the loop that executes a stored proc:

Process_Raw

What syntax do I use inside the SQL Statment box in the Execute SQL Task?

TIA

Joe B

June 16, 2008 3:55 PM
 

jamie.thomson said:

Joe,

I'm a bit confused. You have enumerated the filename of a file into a variable (that bit I get).

I don't understand the relevance this has to calling a stored procedure.

Could you elaborate?

-Jamie

June 16, 2008 4:21 PM
 

jbanko said:

Excuse my nobbieness here. I've done a lot with SQL and am now trying to apply it to SSIS so here goes.

I have a SP which is called Process_Raw. It takes as a single parameter a path to a file. It reads EDI data from a file and breaks it into columns. That part works fine. We'd like to automate it so that as files appear in a directory they get run through the SP and  then moved to another archive folder. I've put an Execute SQL Task inside a ForEach container and have pointed the container to the folder location where the files arrive. Since we don't know exactly when or how many of the files will arrive we want to setup a SSIS package to run each hour from midnight until 3 AM. Inside the ForEach container I have defined a variable User::Filename which I assume will contain the path and filename for each file found in the source folder. I'd like to run the SP on each file (takes under 10 seconds for each file) and then moved the file to another folder.

Does all that make sense???? BTW, THANKS for the response!!!

Joe B

June 16, 2008 5:02 PM
 

jamie.thomson said:

Joe,

OK, I get it now.

So the question is, "how do I pass a string value, that is stored in a variable, as a paramemter in a stored procedure?' Correct?

I think the answer is here: http://www.sqlis.com/58.aspx

In the "Usage Examples" section of that article you can see various examples of passing parameters. You do it via the 'arameter Mapping' tab.

-Jamie

June 16, 2008 5:10 PM
 

jbanko said:

Thanks Jaime. I'll take a look!

Joe B

June 16, 2008 5:17 PM
 

jbanko said:

That did it!

Thanks again Jaime

Joe B

June 16, 2008 5:57 PM
 

Tom Fish said:

Someone above asked this, but I didn't see it addressed.  I don't want to hard code the directory path that holds the files, I would rather set it to a variable.  I don't see an easy way to handle that.  Any ideas?

June 18, 2008 5:47 PM
 

jamie.thomson said:

Tom,

Check this out:

Expressions on ForEach enumerators

(http://blogs.conchango.com/jamiethomson/archive/2006/10/13/SSIS_3A00_-Expressions-on-ForEach-enumerators.aspx)

-Jamie

[3rd time I've been asked this in 2 days. Weird!!! :) ]

June 18, 2008 5:54 PM
 

Tom Fish said:

Awesome, thanks!  I saw the Expressions, but didn't understand how to use it.  Thanks for the info.

June 18, 2008 8:59 PM
 

Svenster said:

Hi

Im using the for each file enumerator, and also the expressions for a dynamic directory and everything works, although for some reason when running from SQL as a package it takes around 2mins to find the one file in the directroy, whereas in the SSIS IDE its pretty much instant, any ideas???

July 24, 2008 1:04 PM
 

Anshu said:

All,

Mine is a very basic package that pulls data from a UTF-8 encoded source file into the SQL Server 2005 database.

Plot:

a) This file is Message Tracking Log file from MS Exchange Server - The first data row in this file starts at 5th row of the file and the fourth row of this file has column header information which is prefixed with a string '#Fields:'

b) The data is available in 21 columns, delimited by a comma (,).

c) Out of these 21 columns I need to pull all the data under only 10 columns (not consecutive).

Scene 1:

a) I created required connection managers for - i) Flat source file, and ii) OLE DB destination table

b) The Data Flow Task (in control flow) comprises of following components to be executed

- Flat File Source, here I have selected only required 10 columns and unchecked rest of the columns

- Data conversion, which converts data from non-unicode format (UTF-8) to Unicode format

- OLE DB destination, which essentially is a database table with 10 columns (given same names as in source file, to avoid any confusion). Here the chosen data from desired columns is finally loaded correctly.

Everything working fine till now, task gets executed and the required data is pulled through to the database.

Scene 2:

a) Added a For Each Loop container around the task created

b) Added the variable for file location and file name

c) Task still gets executed with enumerator picking all files placed at the specified location

d) Data gets loaded in the database table under 10 (desired) columns.

Issue: Data from source file is picked up from first 10 consecutive columns instead of desired columns chosen in the Flat File Source Data Flow component.

My understanding on this error: is that because the task picks source file based on the variable with source file location and the data column details cannot be manipulated in the connection manager, thus the problem.

Can someone else understand and help me on this?

Please ask if you need more details. Though I have tried to explain my question elaborately, it might still be bit ambiguous given my level of experience with the tool (SSIS) and jargons used.

November 24, 2008 3:02 PM
 

Scott-Wallace said:

I am trying to utilize this example but I keep receiving an error:

Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

August 27, 2009 7:40 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems