Processing a Microsoft Queue :: SAS(R) Data Integration Studio 4.5: User's Guide

Problem

You want to write rows from a file into a Microsoft message queue. Then, you need to read the messages back from the queue and write them into a target table.

Solution

You can use the Microsoft Queue Writer transformation in SAS Data Integration Studio to write the data to the message queue. Then, you can use the Microsoft Queue Reader transformation to read the message from the queue and populate them into a target table. Perform the following tasks:

Tasks

Create the Microsoft Queue Job

Perform the following steps to create and populate the job:

Create an empty job.
Select and drag the Microsoft Queue Writer transformation from the Access folder in the Transformations tree into the empty job on the Diagram tab in the Job Editor window.
Connect the source table to the input port of the Microsoft Queue Writer transformation.
Connect the queue to the output port of the Microsoft Queue Writer transformation.
Drag the Microsoft Queue Reader transformation onto the Diagram tab in the Job Editor window.
Connect the queue to the Microsoft Queue Reader transformation.
Because you want to have a permanent target table to contain the output for the transformation, right-click the temporary work table that is attached to the transformation and click Replace in the pop-up menu. Then, use the Table Selector window to select the target table for the job. The target table must be registered in SAS Data Integration Studio. (For more information about temporary work tables, see Working with Default Temporary Output Tables.)

The job resembles the sample shown in the following display.

Sample Microsoft Message Queue Process Flow

The source table for the sample job is named CAKE. The target table is named CAKE2, and the queue is named cypress\venu\nontransactional.

Configure and Run the Microsoft Queue Job

Perform the following steps to configure the job:

Open the Queue Options tab of the properties window for the Microsoft Queue Writer transformation.
Specify the source for the queue. The sample job uses a file. You can also use text or a table as the source.
Open the Queue Options tab of the properties window for the Microsoft Queue Reader transformation.
Specify the target for the queue. The sample job uses a file. You can also use text or a table as the target.
Run the job. If you are prompted to do so, enter a user ID and password for the default SAS Application Server that generates and runs SAS code for the job. The server executes the SAS code for the job. The following display shows that the job runs successfully.

Sample Completed Microsoft Message Queue Job

Verify the Microsoft Queue Job

Perform the following steps to verify the results of the queue job:

Examine the data in the source file.
Access the View Data window for the target table. A sample target table is shown in the following example.

Target Table Data for the Sample Job
Confirm that the source file and the target table contain identical data. This means that the data was transferred successfully through the Microsoft message queue.