How to achieve parallelism in BULK INSERT with OLE DB source and target objects

Question

How to achieve parallelism in BULK INSERT with OLE DB source and target objects

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 1

if you're determined to use SSIS Data Flow, one more iteration to try is creating a Stored Proc that only does the SELECT and put that in the OleDB source

svenflock SSC-Addicted Points: 459 More actions · Answer 2

Wow, never thought about that. But how does it work? If I write the SELECT statement in the SQL task and use a object variable as full return set how do I access the variable or the object in the data source? How performant is using the object variable?

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 3

not quite what I'm suggesting... I think that path would end up in the same place you're at now. I'm saying create a stored proc in the Source DB, in the OLE DB Source of the Data flow, set it to SQL Command and put in "EXEC <i>YourStoredProc</i>;" as the Command text.

svenflock SSC-Addicted Points: 459 More actions · Answer 4

Ok, now I got you. I'll try it tomorrow when I am back at work :-). Hope that this will bring the benefit I was looking for.

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 5

I was talking about this. I hope you will find something useful here.

https://sqlsunday.com/2018/08/09/parallel-and-serial-operators/

Although you have a simple SELECT but if you have used SORT operation in the the reverse order of Index or the table doesn't has the index at all then that may also be contributor to the sequencial plan. Additionally, check if you have used Scalar Function, TOP etc. which causes the sequencial plan.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

svenflock SSC-Addicted Points: 459 More actions · Answer 6

Thanks for the link. I do not use any of such operators. It is just a selection (simple copy of the source table), so no limitation from that side.

If I look at the query plan for the SELECT, the data is read sequential. If I use a INSERT INTO with TABLOCK or a SELECT INTO I get the parallel execution plan. At least it is consequent that SSIS reads it sequentially. For some reason, a simple SELECT cannot use multiple threads.