December 12, 2008 at 2:31 pm
Hi all, I have a need to create a SSIS process that will iterate thourgh a directory and subfolders and based on a filename execute a package to process that particular file.
Example: c:\vendorplacedfiles\2008-12-10 - then there will be five files within each subfolder - clients.txt, episodes.txt.servives.txt, addresses.txt. indirectservices.txt. There may be one subfolder or possibly multiple subfolders - the subfolders will have different days.
I've figured out how to pass the path name to a child package using a variable and I know how to iterate through the folders using a foreach loop. I have a script-task that looks for a file and then executes a Execute-Package-task - this works fine in testing just one file but if i add more files it will pass a success and try to execute the execute-package-task - if i set the script-task taskresult to fail (if the file is not correct for that lookup) then it fails the foreach process and what I would like is to branch to another task to check for a different file name etc. It would be grerat if the tasks could complete single order before firing of the next package so that if subfolders exists the pacakges don't get into contention - is there a way to accomplish this? some general direction would be greatly appreciated
Also the packages that will process the files do the following 1. truncates a database table, 2. reads a flat file 3. uses derived column to do some translation, 4. script componant to reformat dates,5. data conversion to get into database format 6. ole db to insert into a table - each of these work fine
December 12, 2008 at 3:05 pm
I am trying to understand you question but I think what you are asking is you have a structure like this:
Folder1
File1.txt
File2.txt
Folder2
File1.txt
You want to loop through all folders processing each file differently based on the file name?
In this case I would consider :
1. Create a for each loop for the folders (I think you have this)
2. Create a for each loop for the files in the folder (I think you have this)
3. Add a variable for each file name you want to handle as Boolean variables
4. Within the file loop create a script task to evaluate the file name
4.1 based on the file name set the variable associated with the file name to true
5. Create a data flow task for every different file type you want to handle
5.1 pass the file path to this task to tell is where to find the file based on a variable
6. Connect all of the data flow tasks to the script task using success and the criteria of its variable for the join
This way each file is processed separately. Obviously if you want parallel processing you would create several loops and only allow them to process one file type to make sure that only one file type at a time is processed.
Hope this helps and tell me if I am not understanding where you are going with your question.
December 12, 2008 at 3:14 pm
Hi thanks for the response - yes you pretty much got it right. I'll give this a try - looks like it might do what i want. I'll post back later.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply