Message Queue Integration which inserts the information into an SQL Database
The goal of this example is to create a Message Queue integration that includes an Insert Database Record action to be able to save the information for the current printed record.
Microsoft Message Queuing (MSMQ) can use XML Queue Message format, and BarTender supports this format natively from BarTender 2019 to be able to gather valuable information about the print job.
Unlike other types of integrations, this integration takes XML data passed by MSMQ and breaks it apart into variables that can be passed to a label file.
BarTender 2019 and later.
A company needs to send a print job using XML data through its Message Queue service. The print job will execute when data — in this case, XML data — is sent over the Microsoft Message Queue, and read by the BarTender Integration Service. They need to then insert the records from the print job into a Microsoft SQL database.
The following procedure describes how to set up, deploy, and verify the integration for the scenario outlined above.
Setting up the label
Since the Integration can recognize the structure of the XML, you can map the XML records directly to Named Data Sources in the document. As such, this type of label does not use a database but instead uses Named Data Sources to handle all its data. This will go over the basics of how to set up this label. For more information, see Named Data Sources in the help file.
- Create a new label.
- Create a text object on the label design area.
- Right-click on the label and select Properties.
- Select the data source from the list. It'll say "Sample Text."
- Click the box next to the Name field.
- In the dialog, type a unique name and click OK. For this example, use ORDERNUM.
- Ensure the Type is set to Embedded Data.
- When creating more text objects, the Named Data Source dialog will look different. When you click the box next to the Name field, bubble in the first option to create a new name. For example, use CUSTOMER.
- For this example, create an additional Named Data Source and use CITY as the name.
- Once you have created as many Named Data Sources as you wish, save the label.
Create the XML trigger data
For your integration, you will need an XML formatted trigger to send on MSMQ. The XML field names do not need to match your Named Data Source names, as you'll be matching the fields to the Named Data Sources here in the integration. These XML field names will become a variable in your integration automatically. Make sure that your field names do not use spaces.
Here is an example:
Creating the integration
- Open Integration Builder and select Create New Integration.
- On the new integration dialog, select Message Queue Integration and click OK.
- First, specify user credentials on the Integration tab. The specified user will need to have sufficient permissions to access the Message Queue used. Further information can be found in the article Troubleshooting Guide: Integrations.
- On the left-hand menu, select Input Data, then change Input Data Format to XML Variables.
Next, set up the For Each Database Record action.
After the data is received, the integration will need to know how to handle these records coming in.
- On the left-hand menu, click the + button next to Actions.
- In the dialog, click the Database category then select For Each Database Record and then click OK.
- Click the Print Document action on the Actions list and drag it underneath the For Each Database Record action until it indents. This will make the Print Document action a child of this action.
- The For Each Database Record with a Print Document action nested as its child will ensure that the XML data is correctly processed during print time.
- Select the Print Document action, then click on the Named Data Sources tab on the right-hand side.
- Check Specify values for named data sources and enter the Name and Value for each Named Data Source.
Setting up the SQL Database
- Click the + button next to Action again.
- Click the Database category then select Insert Database Record then click OK.
- The Insert Database Record action is also nested as a child action under the For Each Database Record Action and takes place after the Print Document action so that the variables from the print job will be updated in the database.
- NOTE: The Insert Database Record action supports only relational database types. For this action, the Database Setup wizard does not display non-relational database types, such as text, Access, and Excel, or any named database connection that references non-relational database types.
- After adding the Insert Database Record action, on the Insert Database Record tab, click on Database Connection Setup.
- For this example, we will use a local Microsoft SQL Server instance, so we select Microsoft SQL Server and then click Next.
- On the next page, we enter our connection information for the server, the authentication mode, and the database we want to use:
- For more information about how to use the Database Setup wizard, refer to Database Setup Dialog.
- After the database connection is set up, we will need to specify that the variables saved in the print job are the ones used to update the record values in the database, so we select each Column that we want to use and input the variables as the Value to use as follows:
- Save your integration.
- Click the Test Integration button on the toolbar at the top.
- Click the green Start button in the top right corner.
- When the XML data is received on the specified message queue, the integration will trigger, a label will be printed, and the data will be inserted into the database.
How to use multiple records with a socket or web service POST integration
How to create a CSV integration using Named Data Sources