How to configure the Transform Data task for Global Variable Messages (Enterprise Manager)

To configure the Transform Data task for global variable messages

1. Click Not Used, and then CTRL-click Corporate.
   
   
2. On the Task toolbar, click Transform Data Task, and then on the Data Transformation Services (DTS) design sheet, double-click the resulting arrow.
   
   
3. In the Data Transformation Properties dialog box, do the following:
   1. In the Description box, type Insert One Employee.
      
      
   2. Under Connection, click SQL query, and then in the text box, type SELECT 'xxx' AS xxx.

      This generates a source rowset with one row in it. As a result, the insert will be attempted exactly once.

4. Click the Destination tab, and then in the Table name list, select the Employee table.
   
   
5. Click the Transformations tab, and then do the following:
   1. Click Delete All to clear any default transformations.
      
      
   2. In the Destination list, click EmployeeID, and then CTRL-click EmployeeName.
      
      
   3. Click New.
6. In the Create New Transformation dialog box, click ActiveX Script.
   
   
7. In the Transformation Options dialog box, click Properties.
   
   
8. In the Active X Script Transformation Properties dialog box, under Entry function, type the following:

   DTSDestination ("EmployeeID") = DTSGlobalVariables("ID")
   DTSDestination("EmployeeName") = DTSGlobalVariables("Name")
   

9. Right-click the Transform Data task, and then click Workflow Properties.
   
   
10. In the Workflow Properties dialog box, click the Options tab, and then do the following:
    • Select the Join transaction if present check box.
      
      
    • Select the Commit transaction on successful completion of this step check box.
      
      
    • Clear the Rollback transaction on failure check box.
11. Click the Precedence tab, and then add a new entry with Source Step set to New Employee and Precedence set to Success.