Using Global Variable Messages to Queue Database Updates
The Add New Employees package uses global variable messages to take an employee ID and name from a queue and add the employee to the corporate database. It continues to add employees until the queue is emptied, at which point it stops.
Follow these instructions to create and run this package.
Creating and Configuring the Add New Employees Package
The new package must be created and named. In addition, package properties must be set so that a single failure does not prevent the rest of the new employees from being added.
To create and configure the Add New Employees package
Configuring the New Employee Task
New Employee is a Message Queue task. When run, the New Employee task checks for a global variable message. If one is on the queue, two global variables, ID and Name, are brought into the context of this package and a Transform Data task is triggered.
To configure the New Employee task
Creating Two Database Connections
This example works with a database named Corporate containing a table named Employee. The Employee table has two columns: EmployeeID and EmployeeName. EmployeeID serves as the primary key.
After a message has been received, a Transform Data task is used to load the new employee into the Employee table. The Transform Data task requires two connections. In this case, because data comes from global variables instead of a database table, the first connection will not be used.
Place two database connections on the Data Transformation Services (DTS) Designer workspace. Assign both to the Corporate database. Name the first connection Not Used and name the second connection Corporate.
Note This operation can also be accomplished using an Execute SQL task containing one INSERT statement with two input parameters filled by the global variable values. In that case, you need one connection and an Execute SQL task, rather than two connections and a Transform Data task. For more information, see Execute SQL Task.
Configuring the Transform Data Task
The Transform Data task is triggered after receipt of a global variables message. Its job is to insert a single row in the Employee table. You need to:
- Fill destination columns from global variables.
- Ensure that exactly one destination row is written.
To configure the Transform Data task for global variable messages
Configuring the Log Bad Update Task
Log Bad Update is a Message Queue task. It is triggered on a failure in the database update. When executed, it sends the failing ID and Name to another queue for later review.
To configure the Log Bad Update task
Creating and Configuring Two Loop Tasks
Two Loop tasks must be created. (If a single task were set up to follow both Log Bad Update and Transform Data Task, it would never run because at no time would both precedents be satisfied.) You cause the package to loop by accessing the first step with a Dynamic Properties task and changing its status to waiting. The only difference between the two loop tasks is the identity of the precedent task.
To configure one Loop task
Running the Package
Build another package that sends ID/Name pairs to your queue. (Use the Log Bad Update task as a model.) Execute this package. Run the Add New Employees package. As New Employee receives the message, the Transform Data task inserts the new employee, and the Loop task returns control to the first step., At this time the package terminates. If a previously existing employee is sent to the queue, you should see the duplicate key fail the insert, and trigger the Log Bad Update task.