May 19, 2011 at 7:02 am
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
May 20, 2011 at 11:54 am
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.
May 20, 2011 at 12:11 pm
Let the queue have only one queue reader. You set it as an option during the create queue command.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 20, 2011 at 12:19 pm
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.
May 20, 2011 at 12:20 pm
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. 🙂
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 21, 2011 at 3:21 am
You could check if your Activation Stored proc is running before processing the next message.
May 23, 2011 at 12:21 am
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
May 23, 2011 at 12:27 am
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???
May 23, 2011 at 10:06 am
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.
September 7, 2011 at 1:27 am
Hi,
how can i call ssis package from service broker queue.
September 7, 2011 at 1:29 am
Please start a new thread.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply