Asychronous Interator?
I have written a simple SSIS package to import csv files. There are quite a few csv's to import into SQL Server and the package could be modified(I suppose) to interate the contents of a folder for csv files and then use a'Foreach Loop Container' to import each file in sequence. However, in this case, due to the large number and size of the csv files, we would like to be able to import the files (up to a max number of parallel executions) in parallel... asynchrously. There are plenty of server resources available to cope with the load. The names of the csv file will vary so really need to interate a folder. Can anyone suggest how I can do this? Thanks, Zarty
July 1st, 2008 3:56pm

Although SSIS provides simple parallelism by having multiple tasks execute at the same time, there is no simple way that I am aware of to build the "dynamic parallelism" you're describing from within a package. With that said, there are a few different ways you could approach this: Using an external process (there are lots of ways to do this, but you'd need to write the code yourself) split the input CSV files into n different folders, where n is the number of parallel executions you want.Then, inside your SSIS package, have n Foreach Loop containers, each one looping over one of the folders. Use your existing package, designed to load a single text file. Build a .NET application that uses a thread pool and the SSIS .NET API to execute multiple instances of the package in package in parallel, setting whatever properties are needed (such as the path to the CSV file) through the API before execution. Another approach that may be useful, even though it does not address your stated goals, would be to use the MULTIFLATFILE connection manager to load all of the files in the folder in one batch. The advantage of this is that there is only one instance of the data flow created, with one set of buffers and other data flow resources, so you can often get significantly better performance than looping over files and loading each one individually. I don't know if it will make sense for you, but it's worth looking at. Good luck!
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2008 4:28pm

Interesting suggestions, thank you. I was looking at how SSIS will run tasks in parallel by simply placing them in a container without linking them in a sequence. Also a tree hierarchy where the flow branches out in parallel. All that is fine but it would be nice to have an 'asynchronous iterator' of some kind such that the same control/data flow items could be kicked off together but with different parameters/variables. Thanks again.
July 1st, 2008 5:32pm

I agree that this would be a great feature, but unfortunately, nothing with these characteristics exists in SSIS today.
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2008 6:19pm

Hello Zarty, There is already a solution on the market, which allows execution of multiple For Each Loop (FEL) iterations in parallel. It is called CozyRoc Parallel Loop Task. You have to select existing FEL and then the task will execute its elements in parallel. In tests, a CPU intensive sequential process when executed in parallel on 4-core machine was executed 3 times faster compared to the sequential.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
December 3rd, 2011 8:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics