none
SSIS and MSMQ

    Question

  • I am a novice in SQL Server 2008, so if this question seems fundamental, I apologize ahead of time.

    I have a general understanding of how SSIS packages interact with MSMQ by sending/receiving

    messages from packages.  The scenario I'm thinking of is, let's say you are sending messages to the queue

    from many different packages that perform different tasks from different computers, and each package sends a message

    to the queue.  You have multiple offices sending text files of data which will be used later by another package to load the

    data into the database, and each time the package executes, it writes a message for each office file.  How does the "receiving"

    package know which messages belong to it?  (I hope I'm asking this correctly)

    Wednesday, February 20, 2013 2:24 AM

Answers

  • Hi,

    With MSMQ there are two types of queues Private and Public. As the name suggests the Private Queue keeps information on local system and can be accessed by path name which requires Computer name with Private$ and the queue name all separated by \. The Public Queue on the other hand is registered in the Active directory. These are available from other domains.

    You can use a private queue where the computer name will be mentioned to identitify which message in the queue belong to which package based on the assumption that this recieving package runs on different computers.

    Thanks,

    Amit

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    Wednesday, February 20, 2013 7:55 AM
  • There is two options which you need to configure correctly inorder to get this right.

    String message and string message variable.

    Also you can check this email.

    http://blogs.msdn.com/b/martijnh/archive/2010/06/10/reading-msmq-from-within-ssis-packages.aspx


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    Wednesday, February 20, 2013 7:53 PM
  • Hi,

    Normally, In MSMQ the approach will be FIFO so when a message comes in first it is the one that goes out first

    When a package is generating the messages, it will append the connection configured for the package to the msgInfo and store it in Queue. When the recieving package is executed it will process the first msg in the queue and if the connection property am not exactly sure, is same then it retirieves the message or it will remove the message from the queue & store it in temp storage and process the next one and dump back the message from the temp space. You have to dig in much in this if you want to know the exact configuration happenning in the package.

    Thanks,

    Amit

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:25 PM
    Thursday, February 21, 2013 9:05 AM

All replies

  • Hi,

    With MSMQ there are two types of queues Private and Public. As the name suggests the Private Queue keeps information on local system and can be accessed by path name which requires Computer name with Private$ and the queue name all separated by \. The Public Queue on the other hand is registered in the Active directory. These are available from other domains.

    You can use a private queue where the computer name will be mentioned to identitify which message in the queue belong to which package based on the assumption that this recieving package runs on different computers.

    Thanks,

    Amit

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    Wednesday, February 20, 2013 7:55 AM
  • Hi Amit!

    Thank you for your response.

    I did set up a private queue on machine to test this out and I ran the same package multiple times to simulate the text files coming from the district offices through the DownloadOfficeFiles (as an example) package, which generated multiple queue messages from the same package for the different test offices.  Then, I also fired off the same package multiple times for each district office, through the LoadOfficeFiles (as an example) package, which received the messages, processed the load, and deleted the messages from the queue, which is exactly what I want it to do.  How does the LoadOfficeFiles package know which messages to pick up from all the messages in the queue, assuming there are other messages from other processes (report messages, delete messages, archive messages, etc.)?  Is there an imbedded message in the DownloadOfficeFiles message that LoadOfficeFiles recognizes that, dor example, the 3rd, 7th, and 10th messsages are the messages it needs to receive and process?  Once again, I apologize if this is fundamental, but I'm trying to understand how the process works, and I thank you for your patience with this novice.

    Thank you!

    Jim

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    • Unmarked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    Wednesday, February 20, 2013 5:34 PM
  • There is two options which you need to configure correctly inorder to get this right.

    String message and string message variable.

    Also you can check this email.

    http://blogs.msdn.com/b/martijnh/archive/2010/06/10/reading-msmq-from-within-ssis-packages.aspx


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:26 PM
    Wednesday, February 20, 2013 7:53 PM
  • Hi,

    Normally, In MSMQ the approach will be FIFO so when a message comes in first it is the one that goes out first

    When a package is generating the messages, it will append the connection configured for the package to the msgInfo and store it in Queue. When the recieving package is executed it will process the first msg in the queue and if the connection property am not exactly sure, is same then it retirieves the message or it will remove the message from the queue & store it in temp storage and process the next one and dump back the message from the temp space. You have to dig in much in this if you want to know the exact configuration happenning in the package.

    Thanks,

    Amit

    • Marked as answer by Jimbo0725 Thursday, February 21, 2013 5:25 PM
    Thursday, February 21, 2013 9:05 AM