Looping through folders (using Foreach Folder enumerator)
-
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?
-
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.
-
This issue is solved (I had to make "loop in the loop"). But tnx anyway!
-
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