"Paralellizing" file loads

I have a package that reads a table that has a list of files that I need to load into a table that arrive every night. These files range from 50mb to 1.5gb, The entire process to load and transform is taking about 50mins, which is about a 300% increase from our current production environment. However I am looking at ways to improve performance by loading all of the data into the staging table at the same time.

Is this possible, and do you guys think it would improve performance significantly?

Can you provide more details about the source target picture?

So, many files going into a single staging table?

If so, You may want to try having several threads reading from the files and inserting into the target table; perhaps using multiple dataflows.

|||

The source are about 27 files arriving from a customer to a network drive. Currently we start the package when all the files have arrived and we load 1 file at a time into a table using a simple FileConnection->OLE DB Destination.

I have a Foreach Container (files) that loops on an ADO.NET Recordset. Inside the Forloop container it starts a package which loads the file.

So my question is how could I launch that package from within my package "asynchronously"?

|||Unforntunatly, Using a For Each loop will not help you on processecing several files at the time, which I think is the only way to speed the process up.|||

There is a possibility here, Rafael and Jwelch have given a bunch of clues in previous forums to speed up, it all depends and I have clues too,

1) Incase you have a file meant for a single table you can have few parallel dataflow extracts.

2) If there are list of files populated into each table then we can group the file according to the pattern (use wild cards or expression to assign file format) and then run few Parallel extracts using ForEach loop for each of the file groups

3) If you are loading only a single table then parallel insertion wont help, because table lock might apply when rows are getting imported from a file.

Thanks

Subhash Subramanyam