I know 2008 is outdated but a need is a need irrelevant to sources
I am using SSIS 2008 and SQL Server 2008 R2 ,
My requirements are
I used SSIS 5 files bulk loading but it got blocked for a long time any help is appreciated..
To get the best performance in loading a table, we want to shovel the data in by the truckload (table lock). The problem is, there's only room for one truck in the bay. Otherwise, if you want multiple feeds into a table at one time, you're likely looking at throwing data in by the shovel full - that way, 5 workers can be there and their loading won't block each other but the throughput is lessened.
If you're Enterprise Edition, or you want to go old school with a partitioned view on Standard Edition, then you could have each partition/individual table loaded in parallel and then you have N worker processes pouring the data in as fast as the disk subsystem allows and none of the contention you're currently experiencing.
As @David Browne points out, SQL Server supports parallel bulk loads to unindexed heap tables
To get BU lock you need to specify the TABLOCK option with each bulk import stream without blocking other bulk import streams
In your OLE DB Destination, this will be a Fast Load (default) and check the Table lock checkbox. As the msdn article calls out, the destination table needs to be empty or the lock will be IX-Tab and not BU-tab
Yes, you can load from multiple sources. I read from multiple sources at the same time, and write using the fast load option.
