locked
MSMQ Task to Receive Multiple Messages RRS feed

  • Question

  • I would like to pull multiple string messages (each with a similar format) from from MSMQ using the MSMQ Task. Is it possible to use a For Each Container to do this? Any other suggestions? I do have a DTS Active X Script that does the job but would like to use the MSMQ Task if possible or at the very least update the script to VB .Net.

    R Campbell

    Wednesday, August 15, 2012 4:18 PM

Answers

  • My stake on this is that you should resort to polling the MSMQ store using the Script Task to at least find out how many messages are pending to be downloaded in the queue, capture the number to iterate over for the ForEach Loop, or consume them at once. Thing is SSIS's Task does not see that there are no more messages in the queue left.

    One other technique I can think of is to attach an Event Handler to this DFT (where you work with the MSMQ) and set its Propagate OnError property to false thus effectively inhibiting the error (more here http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/).


    Arthur My Blog

    • Marked as answer by Dick Campbell Thursday, August 16, 2012 2:56 AM
    Thursday, August 16, 2012 2:45 AM

All replies

  • Well I have got something working using a For Loop Container.

    I have the MSMQ Task Receive Time Out set to raise an error and a script in the the error event that forces the For Loop to end.

    The only thing that I don't like is that I get an error every time the package runs even though it was sucessful. Is there something that I can do in the error handling script to "cancel" the error? Is there a more elegant way to detect when the queue is empty?


    R Campbell



    Thursday, August 16, 2012 1:20 AM
  • My stake on this is that you should resort to polling the MSMQ store using the Script Task to at least find out how many messages are pending to be downloaded in the queue, capture the number to iterate over for the ForEach Loop, or consume them at once. Thing is SSIS's Task does not see that there are no more messages in the queue left.

    One other technique I can think of is to attach an Event Handler to this DFT (where you work with the MSMQ) and set its Propagate OnError property to false thus effectively inhibiting the error (more here http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/).


    Arthur My Blog

    • Marked as answer by Dick Campbell Thursday, August 16, 2012 2:56 AM
    Thursday, August 16, 2012 2:45 AM
  • My stake on this is that you should resort to polling the MSMQ store using the Script Task to at least find out how many messages are pending to be downloaded in the queue, capture the number to iterate over for the ForEach Loop, or consume them at once. Thing is SSIS's Task does not see that there are no more messages in the queue left.

    One other technique I can think of is to attach an Event Handler to this DFT (where you work with the MSMQ) and set its Propagate OnError property to false thus effectively inhibiting the error (more here http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/).


    Arthur My Blog

    Thanks for that, now the MSMQ fails but not the For Loop or the package. Is that what to expect?

    I take your point about checking the size of the queue first. Waiting for a time out and forcing an error is a bit brutal.


    R Campbell

    Thursday, August 16, 2012 3:00 AM
  • Yes, this is what to expect, you can go even further and make this package always to succeed, but I 'd say this is too extreme.

    Arthur My Blog

    Thursday, August 16, 2012 3:03 AM
  • Yes, this is what to expect, you can go even further and make this package always to succeed, but I 'd say this is too extreme.

    Arthur My Blog

    Yes, it works quite well as it is and I wonder whether making it more "elegant" is just gold plating. Thanks for your help.

    R Campbell

    Thursday, August 16, 2012 12:50 PM
  • To make it more elegant you need to take the Script Task-way alley path Dick.

    It will give you the opportunity to do reconnaissance on the amount of messages to process (think of a package trying to process an empty queue on the very 1st round) as oppose to brutally processing it regardless chocking on the "end of queue" state.


    Arthur My Blog

    Thursday, August 16, 2012 1:41 PM
  • To make it more elegant you need to take the Script Task-way alley path Dick.

    It will give you the opportunity to do reconnaissance on the amount of messages to process (think of a package trying to process an empty queue on the very 1st round) as oppose to brutally processing it regardless chocking on the "end of queue" state.


    Arthur My Blog

    I have now added a script step to get the number of messages and store it in a variable for use in the For Loop. Strangely there is no size or length property for a queue. I had to use MessageEnumerator2 to step through the messages to get a count. I have left the error event in place as a backstop but there is no error now when the package succeeds. The situation reminded of my favourite ODBC error, "Error, sucessful completion". 

    R Campbell

    Friday, August 17, 2012 6:08 PM
  • Glad you did such a wonderful progress, and yes, I did not tell you that there is size or length property for a queue, my fault, just did not think you would look into that, but glad you got to the MessageEnumerator2 yourself.

    Arthur My Blog

    Friday, August 17, 2012 6:33 PM