October 26, 2024 at 3:01 am
hi everyone
I have a SP that is run in SSIS. That particular SP is basically running a bunch of SP. See below for code. SP1 and SP2 are independent of each other so they can definitely be run in parallel. They all read data from the same table but each writes to a different table. I know that SS has logic that automatically tries to run items in parallel when it can but I would like to have more control over the entire process. Specifically, I would like to explicitly ask SS to run these SP1 and SP2 in parallel and once they are done then run SP3 and SP4 in serial within the below SP. Is this possible? If yes, how would I do that?
/****** Object: StoredProcedure [dbo].[RunStoredProcedures] Script Date: 2024-10-25 7:52:43 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[RunStoredProcedures]
as
EXEC dbo.SP1
EXEC dbo.SP2
EXEC dbo.SP3
EXEC dbo.SP4
Thank you
October 26, 2024 at 10:52 am
As far as I know, you cannot force parallel execution of procs within a single session. Options include:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 26, 2024 at 12:21 pm
and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
October 26, 2024 at 3:44 pm
and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?
October 27, 2024 at 11:23 am
frederico_fonseca wrote:and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?
Use multiple ExecuteSQL tasks. Each task runs a proc. If you connect them with precedence constraints, they run in series, but if you leave them unconnected, they run in parallel. No code (other than the T-SQL to execute the proc) is required.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 28, 2024 at 3:48 am
water490 wrote:frederico_fonseca wrote:and your C# code that you were using to execute the SP's is also a good place to execute them in parallel
My SSIS package runs RunStoredProcedures. If I want to run SP1 and SP2 in parallel in SSIS then how would I do that? Do you have a sample code I can use to learn from?
Use multiple ExecuteSQL tasks. Each task runs a proc. If you connect them with precedence constraints, they run in series, but if you leave them unconnected, they run in parallel. No code (other than the T-SQL to execute the proc) is required.
perfect. thank you! I have a few more questions.
October 28, 2024 at 7:02 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply