Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sequentially processing messages in Service Broker Queue Expand / Collapse
Author
Message
Posted Thursday, May 19, 2011 7:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
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
Post #1111654
Posted Friday, May 20, 2011 11:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
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.
Post #1112618
Posted Friday, May 20, 2011 12:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1112634
Posted Friday, May 20, 2011 12:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
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.
Post #1112637
Posted Friday, May 20, 2011 12:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1112639
Posted Saturday, May 21, 2011 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1112867
Posted Monday, May 23, 2011 12:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
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
Post #1113133
Posted Monday, May 23, 2011 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 5:21 AM
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???
Post #1113134
Posted Monday, May 23, 2011 10:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
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.
Post #1113437
Posted Wednesday, September 7, 2011 1:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 9:37 AM
Points: 20, Visits: 581
Hi,
how can i call ssis package from service broker queue.
Post #1170861
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse