Welcome to blogs.conchango.com

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

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

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.

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

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.

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

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

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?

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

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

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.

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

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

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

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 :(

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

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?

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.

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.  

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

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

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.

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

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

Thanks Jaime. I'll take a look!

Joe B

That did it!

Thanks again Jaime

Joe B

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?

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

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

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.