Exercise 5: Adding a transformation to an element in the mapping model

This exercise teaches you how to transform a source element to be compatible with a target element.

Before you begin this exercise, you must complete Exercise 4: Finding relationships between source and target elements.

To add a transformation to an element in the mapping model:

After you use the discover function to find relationships between the elements in your mapping model, follow these steps to add a transformation to a source element so that it will be compatible with the target elements.
  1. Right-click on the mapping line between /SAMPLE/SAMP/DEPARTMENT/DEPTNO and /SAMPLE/SAMP/PROJECT/DEPTNO/DEPTNO in the mapping editor. These elements seem to be a perfect match. You can verify this by selecting Properties from the drop-down menu. The properties page contains useful information about the source column and the target column. In this example, the DEPTNO data type is CHAR in both the source and the target.
  2. But, to ensure that all of the department numbers have lower case characters, add a lower case function to the element. Right-click on the mapping line between /SAMPLE/SAMP/DEPARTMENT/DEPTNO and /SAMPLE/SAMP/PROJECT/DEPTNO/DEPTNO.
  3. Select Transform->Add to open the Expression Builder wizard.
  4. In the Functions field, expand the String functions. Double-click the LCASE(VARCHAR) function. The LCASE function appears in the Transformation expression field as LCASE().
  5. Click in the parenthesis of the function to change the focus. Then double-click on the DEPTNO element in the Columns field. The DEPTNO column appears in the parenthesis.
    The Expression Builder with the lower case function added..
  6. Click OK.
  7. The mapping line changes to show that a transformation has been added:
    The hub on the mapping line changes when you add a transformation, filter, sort expression, or join condition.

The mapping editor displays a visual representation of the mapped relationships. Now you are ready to begin Exercise 6: Saving the mapping model.