Working with Connections and Loops
Connections and loops help define how data within the transform is processed. This section includes the following topics:
Adding Assign Links
Links are arrows that you draw between resources to indicate direction of the data transformation. Assignments are used to map data movement between sources and targets.
To add assign links in the Transformation Editor Model tab
1. Select the Assign Link Mode. Alternatively hold SHIFT key while creating a link (Shift+Minus) Transformation Editor toolbar icon.
2. Click an operation handle, while holding your mouse button down, drag the line to the row you want to connect it to.
3. Save your transform.
Adding Loop Links
Loop links add more operations to your Transformation Editor model to make it easier for you to define iterations through sequences. If one of the endpoints of a loop link is a loop, a new loop operation is created on the Model tab.
To add a loop between two operations in the Transformation Editor Model tab
1. Select the Loop Link Mode. Alternatively hold SHIFT key while creating a link (Shift+Minus) Transformation Editor toolbar icon.
2. Click an operation handle, and while holding your mouse button down, drag the line to the row you want to connect it to.
3. Save your transform.
A new box is drawn on the model to represent the loop. Use this new operation to define the behavior of the loop logic.
Using Auto Map Link Mode
Auto map is a quick way to assign table or view metadata from one operation to another in your transformation. The auto map mode compares the names of the parameters and maps the values that match each other.
To use the auto map feature
1. Open an existing or create a new transform.
2. Make sure that your transform has at least two operations or resources that contain data structures.
3. Select Auto Map Link Mode.
4. Click and drag the mouse to draw a line between two operations with similar parameters.
5. Release the mouse to connect the two operations.
TDV maps the two structures according to the names that match. For example:
6. Keep or delete the mappings as needed for your transformation.
7. Save your transform.
Using Auto Fill Link Mode
Auto fill can be used to assign and replicate a metadata structure from one operation into another in your transform. If the target operation of your auto fill action does not have the parameters that are part of the source operation, they are added to the target operation.
To use the auto fill feature
1. Open an existing or create a new transform.
2. Make sure that your transform has at least two operations or resources that contain data structures as they exist for view, table, or XML schemas.
3. Select Auto Fill Link Mode.
4. Click and drag the mouse to draw a line between two operations with similar parameters.
5. Release the mouse to connect the two operations.
TDV maps the two structures according to the names that match and adds any extra parameters to the target of the auto map. For example:
6. Keep or delete the mappings and parameters as needed for your transformation.
7. Save your transform.
Editing Loop Operations
Loop operations make it easier for you to define looping references and transformations. Edit the loop to define the behavior of the loop logic. When editing, consider the following limits:
• Loop operations are used to generate sequences.
• The size of the target sequence will match the source sequence.
• The content of the target sequence does not necessarily need to match the source.
• Only the size of the target sequence is controlled by the loop.
• Item filters can be used to limit the size of the target sequence.
SQL functions can be used in the Query operation if the query gets pushed to a SQL database for execution. Outside of the Query operation you can use canonical, XQuery or custom functions.
If your loop operation contains an expression that contains illegal characters you must enclose them in double quotes. The following characters are considered illegal:
'\\', '[', ']', '-', '(', ')', '.','^','$','+', '*','?', '{', '}', '|', ':', '<', '>', '<', '='
To edit a loop between two operations in the Transformation Editor Model tab
1. Double-click to select a loop.
Depending on what iterator rows you have connected, the loop editor might have one or more iterator levels already defined.
2. To add a new iterator, click the green circle with a plus symbol in it.
A Name is the name given to the iteration level of the loop which matches the name of the source input of the loop. The Level indicates the nesting level associated with an iterator.
3. To define the filter logic, type text into the Item Filter Field. The item filter is processed every iteration.
Value | Description |
True | If the item filter evaluates to true, then the current loop entries used to generate a target sequence item. If the item filter is true, then the target sequence will contain as many items as in the source sequence. |
False | If it evaluates to false, it will be skipped. |
Complex Expressions | If you provide a more complex expression, output is constrained to data that meets the criteria specified. For example, for totalPrice > 50.0, the loop only outputs entries where the total price is greater than 50. It is only valid for expressions to refer to the input of the loop. |
4. Use the arrows to the right of the Item Filter fields to arrange the Filter Name fields in the order that makes sense for the given loop logic that you need to define.
5. Click Close to save your definitions and close the Loop Iterator Editor.
6. Save your transform.
Adding Cast Operations to Connection Lines
While designing your transform, you might notice that some of the link lines are red or orange, which indicates that there is some error or warning message associated with them that needs fixing. Often the warning message can be fixed by adding a CAST function between the two connected points.
To add cast operations to connection lines
1. Open your transform.
2. Select a connection that is displayed in orange or red.
3. Right click and select Insert Cast.
4. From the Transformation Editor palette, select Cast.
The Cast operation is added to the transform.
5. Double-click the Cast operation.
6. Select the data type that you want your data to become.
7. Specify any additional data type details.
8. Click OK.
9. Save your transform.
