Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequentially processing messages in Service Broker Queue


Sequentially processing messages in Service Broker Queue

Author
Message
prasanthev
prasanthev
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 60
I am using service broker to execute the SSIS package. Queue is created with Activation ON and created an Activation Stored proc which will invoke the SSIS package. Now if i send more than one message using service broker multiple instance of the SSIS package is getting executed simultaneously. Is there a way to configue the queue is such a way that if one message is under process it will wait till that process get completed and then only it will process the next message.


Thanks,
Prasanth
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
That depends how you execute that SSIS package from the activation procedure.

If you can show the code, the I'll get a better understanding on how you receive a message from the queue and how that message is used to execute the package.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
Let the queue have only one queue reader. You set it as an option during the create queue command.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
But it still depends how the SSIS package is executed. If the T-SQL statement that starts the package "finishes" before package execution is completed, the activation procedure will get the next message and start another instance of the SSIS package before the first one has completed.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
Nils Gustav Stråbø (5/20/2011)
But it still depends how the SSIS package is executed. If the T-SQL statement that starts the package "finishes" before package execution is completed, the activation procedure will get the next message and start another instance of the SSIS package before the first one has completed.


Yeeps! Good point, nice catch. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
You could check if your Activation Stored proc is running before processing the next message.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
prasanthev
prasanthev
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 60
Here is the script of my Activation Proc:

CREATE PROCEDURE [dbo].[usp_ExecutePackage]
WITH EXECUTE AS 'dbo'    
AS
BEGIN

SET ARITHABORT ON
set nocount on
set xact_abort on
   
DECLARE @PackageName VARCHAR(500)
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRY
BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM InstTargetQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;


IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';

SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
(@ReplyMsg);

END CONVERSATION @RecvReqDlgHandle;
END

--SELECT @ReplyMsg AS SentReplyMsg;



   IF @RecvReqMsg='<RequestMsg>TestPackage</RequestMsg>'
      EXEC dbo.usp_ExecutePackage_TestPackage
   ELSE IF @RecvReqMsg='<RequestMsg>TestPackage1</RequestMsg>'
      EXEC usp_ExecutePackage_TestPackage1
      

-- Display recieved request.
--SELECT @RecvReplyMsg AS ReceivedReplyMsg;
   
      
COMMIT TRANSACTION;

END TRY
   
BEGIN CATCH
   

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

   INSERT INTO [InstTargetDB].[dbo].[Error_log]
([ERROR_NUMBER]
,[ErrorSeverity]
,[ErrorState]
,[ErrorProcedure]
,[ErrorLine]
,[ErrorMessage])
SELECT
ERROR_NUMBER() AS ErrorNumber,
CAST(ERROR_SEVERITY() AS VARCHAR(100)) AS ErrorSeverity,
CAST(ERROR_STATE()AS VARCHAR(100)) as ErrorState,
CAST(ERROR_PROCEDURE()AS VARCHAR(100)) as ErrorProcedure,
CAST(ERROR_LINE()AS VARCHAR(100)) as ErrorLine,
CAST(ERROR_MESSAGE()AS VARCHAR(2000)) as ErrorMessage;


END CATCH

END
prasanthev
prasanthev
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 60
ALTER QUEUE InstTargetQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = usp_ExecutePackage,
MAX_QUEUE_READERS = 20,
EXECUTE AS 'dbo'
);


Do you mean to say i have to set MAX_QUEUE_READERS = 1 ? So that it will not invoke the Activation Proc if one instance is already executing???
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
How does usp_ExecutePackage_TestPackage and usp_ExecutePackage_TestPackage1 execute the SSIS package? Is control returned to the procedure before the SSIS package has completed? If so, then setting max queue readers to 1 won't help. If the SSIS package has to complete before control is returned to the procedure, then setting max queue readers to 1 will help.
mupparaju78
mupparaju78
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 583
Hi,
how can i call ssis package from service broker queue.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search