2

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

  1. Load multiple source file data using Bulk load in control flow (Each File size is 20 Gig .txt files-- In total 150 Gig for 5 files apprx)
  2. The SQL table is single (Tabledata with 243 columns)

I used SSIS 5 files bulk loading but it got blocked for a long time any help is appreciated..

vikki
  • 23
  • 4
  • Neither of those are "questions". What are you actually asking here? – Thom A Aug 03 '20 at 14:28
  • I have changed from questions to requirements ..hope that makes sense.. Cherrs – vikki Aug 03 '20 at 16:13
  • So, again, what are you asking here? You're posting a question; you need to therefore ask a question. – Thom A Aug 03 '20 at 16:43
  • I need help in the above requirements! – vikki Aug 16 '20 at 18:15
  • But you've marked an answer as a solution, meaning that this problem is resolved. – Thom A Aug 16 '20 at 18:23
  • Making a question as solved, means it's **solved**; meaning your question has been answered. If it's not, either unmark the answer as solved, or ask a new question, which expands on the part you don't have an answer on. You didn't just give him "a point" (upvote?), marking a question as solved is a very different action to an upvote. [How does accepting an answer work?](https://meta.stackexchange.com/q/5234/397219) If you still need help with the requirements, that means the question isn't solved. If so, then what *isn't* solved? What about the answer you say is the solution, isn't? – Thom A Aug 18 '20 at 08:16
  • Note that, as you have marked this question as solved, you are unlikely to get new visits from users, as they often don't view solved questions unless they have the same problem. This is why it's really important you only mark it as sovled when it truly is, or you create a new question, which is related, but **importantly** not a duplicate, they shows the other parts that aren't solved in the problem you're having. – Thom A Aug 18 '20 at 08:21

2 Answers2

1

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • SQL Server does support parallel bulk load to unindexed heap tables. See https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177445(v=sql.105) – David Browne - Microsoft Aug 03 '20 at 14:41
  • 1
    Thanks for the pointer @DavidBrowne-Microsoft Hopefully, I've incorporated the relevant bits into the answer and now I have a fun thing to play with this evening ;) – billinkc Aug 03 '20 at 14:57
0

Yes, you can load from multiple sources. I read from multiple sources at the same time, and write using the fast load option.

enter image description here

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Union all is going to be a partially blocking component so your total throughput might be impacted – billinkc Aug 03 '20 at 15:22
  • @billinkc can you please elaborate on "partially blocking"? – Raj More Aug 03 '20 at 15:58
  • 1
    https://jorgklein.com/2008/02/28/ssis-non-blocking-semi-blocking-and-fully-blocking-components/ When the source buffer rows hit the Union All, they are copied into new buffers and sent along the way so in your screenshot, you have 4 sets of buffers, the three that all run independent and then you have that union all where each original row is copied into a new buffer. – billinkc Aug 03 '20 at 16:41