June 22, 2011 at 10:35 pm
I have 100 files in a Remote System.I need to read 10 files in one time and the remaining at some other time in SSIS 2008.
I know that we can read each file one at a time using Script Task.Is there any other way to do it through existing tasks?
Also,how do i schedule it in such a way that it reads some files at one time and other files some other times?
Thanks in advance
June 22, 2011 at 11:14 pm
Is there something in the filename that differentiates the 10 files from the others? You can use a for each loop with a wildcard file name that is set as an expression.
If you don't like that option how about a dos script that you call in ssis that copies 10 files into a seperate folder that you can loop through with a for each loop. (If you're not great with dos, experts-exchange is a good resource)
June 22, 2011 at 11:37 pm
Using the For Each container seems like a good idea. But How do i schedule it such that some files are read at one time and the others at some other time?
June 22, 2011 at 11:38 pm
Also, suppose v have to count the files that need to be read at one time. Then how do v count how many files are read in the For Each container?
June 23, 2011 at 11:09 am
When you say you need to read in 10 files at once, I assume you mean that want them to run in parallel?
If your workplace/project is open to purchasing third party components, you might look into the Parallel Loop Task from CozyRoc: http://www.cozyroc.com/ssis/parallel-loop-task. I haven't used this tool, but have used some other CozyRoc products and have been satisfied with the quality and support.
If this is not a possibility, you'll need to first catalog the files in the source directory and store that list in some structure (database table, SSIS variable, etc.). This would allow you to keep track of the files in the directory as well as those that are already processed and those currently processing. You would use a "next on deck" task (either a T-SQL task or script task) to get the next available file.
Since you're probably going to have to spin up 10 identical processes, I'd probably create a child package that handles exactly one file (to avoid having to create the source-transformation-destination components 10x). The file name would be passed in through a parent package variable.
To start building this, create a sequence container. In that container, create ten instances of your "next on deck" task I referred to, and connect each one to an Execute Package task that calls the child package described above.
Make sense? Let me know if this works for you, or if you need any additional help.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
June 23, 2011 at 11:26 am
Tim read this differantly then I did, and going back to the orginal request I think his interpertation is correct. Looping through the files will only load them serially.
I will 2nd the "plug" for cozy-roc. I use it all the time and have worked with the orginal developer quite a bit, and find his software to be fantastic.
You could always write your own custom task, as another option, try this link for info on that:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply