Queue based integrations are the way to go when designing a new integration. As you can tell from reading other posts in this Blog and from our training, we recommend that you make use of queue based integrations when you can. Queue based integrations can take advantage of the multi-threading capability of your Scribe Insight server and can have an automatic retry for rows that fail to be processed.
But, what if you created an integration using file, time, or query integration processes before you knew how great queue based integrations were? We don't want you to have wasted all the time you spent creating that integration solution. So here are some steps to follow to preserve all your work if you want to transition your existing integration to one that uses message queues.
The first thing you need to do is create a publisher to feed the new queue based integration. Queue based integrations utilize XML as the source in the DTS as opposed to using a connection directly to a data store. You can review this blog post to find out more info on how to setup and use query publishers. The most important thing that you should do is use the same exact query from the original DTS file in your new query publisher. This will ensure that you get the same data, in the same format, as you did in your original DTS file. It makes your conversion much smoother.
Once your query publisher is set up, you are ready to start changing the DTS file over to use XML as the source. First, create a schema file (XDR) and a sample XML file from the query publisher. Then open your DTS file and use the Connection Manager to add a connection to XML . Define this XML connection as a source, and select Dynamic as the connection type.
Once the connection has been established its time to swap it in as the new source for your DTS file.
When go through this process, you will get a LOT of errors, but, in the end they are worth it. When given the option, select that you want to use the same data links and source query; so that it does not wipe out your data links or source fields. Once you get to the point of changing the source query, you will have to set the XML object as the new source, and define any repeating nodes that you may have in the XML. Again, you may get some errors about re-defining links, source fields going missing, etc., but stay with it, you are almost finished.
When you come to the end of the warning messages, you will see that the source is changed, but, all your data links are now invalid. This occurs because the Workbench cannot validate what field belongs to which source field reference number. For example, let's say you used to have a data link using S3, which was Name, the Workbench cannot figure out what field is now the Name field. This happens because of the way the XML is formatted. Even though the field name is the same to you, to Scribe it's slightly different. What was once a Name on the Account table is now <Account.Name> under the covers; so it does not match 100% to what it was before. Also, on top of this, 7 extra fields are added to the beginning of your field list, so S3 is actually now S10.

DTS Query connected to SQL

DTS Query now connected to XML
This is where some manual labor comes into play, as you must correct each invalid link to change the ?# (?1, ?2, ?3, etc.) to the new reference (S8, S9, S10). This does sound like a lot of work, but it's a whole lot easier than having to re-design every single data link and formula that is in your DTS file. Once you get all the links, including your calculated variables and lookup links, corrected, you are ready to start testing your newly updated DTS.
The last piece of this puzzle is to create a queue based integration process that calls this DTS. When you create the integration process, make sure the message label matches the message label created by the query publisher you created.
At that point, you should be ready to start testing your query publisher to send data to the message queue, and testing your integration process and related DTS file to see if they process messages properly.
If you want to learn more detailed steps about how this process works, please check out the Scribe Inside Track service, which gives you access to a vBook called "Message Queuing" including a step-by-step video guide to converting your DTS files. A subscription to the Inside Track gets you access to premium content including deep dive educational videos, monthly interactive expert panel discussions, and a quarterly Inside Track newsletter. Learn more here - http://www.scribesoft.com/InsideTrack/.