Post reply

Looping through folders (using Foreach Folder enumerator)

  • September 19, 2011 at 3:09 am

    #257403

    Hello!

    I have following scenario:

    Import data from flat file source into an SQL table, than rename the file and archive it in a subfolder

    I use a foreach loop container and it works great within one folder.

    Now I want it to loop through all folders. I tried this solution:

    http://microsoft-ssis.blogspot.com/2011/01/foreach-folder-enumerator.html

    I tried both Open source Component and the script task but I got same error. When running the package, the import file task only comes to the folder, not to the file.

    Let's say, my folders are in C:\Main

    There are folders 1, 2, 3, 4... and so on (C:\Main\1 C:\Main\2 ...)

    In each folder, there are files to be imported. When I'm looping through the files in C:\Main\1 I use Foreach File Enumerator pointed to the C:\Main\1. In the variable mappings I use User::FilePath. I use same variable "User::FilePath" as connection string in my flat file connection. And that works great. But when I try foreach folder enumerator, I got the error: Cannot open the datafile C:\Main\1 so, somehow it never comes to the files, just to the folder. Obviously, I have to change connection string in the flat file connection, but I don't know where to point my flat file source and where to point the foreach folder enumerator? Do I need a new variable? I even need to say that my files don't have .txt extension, it's some backup files, so I need to loop through all files in the folder (extension shouldn't be a problem I guess, because the loop works in one folder...)

    One more thing, if I manage to loop through the folders, I need to use different destination tables for different folders, so files from C:\Main\1 need to be imported into the table named 1, files from C:\Main\2 into the table 2 and so on.... Is it possible to use a variable in my OLE DB connection too?

  • September 19, 2011 at 7:26 am

    #1383296

    Can we see the code for the loop of folders? I think you might be not handling the folder names correctly as you map through the process.

    You should have one for the current folder and one for the current file - at least how my head sees it.

    The first line of code starts with coffee. The last line ends with alcohol.

  • September 20, 2011 at 2:46 am

    #1383673

    This issue is solved (I had to make "loop in the loop"). But tnx anyway! 🙂

  • January 31, 2012 at 10:38 pm

    #1441037

    here is a Custom Foreach Folder Enumerator:

    http://microsoft-ssis.blogspot.com/2012/02/custom-ssis-component-foreach-folder.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply