Input Rules
Input rules are a set of conditions in a dynamic port that define the ports to generate. You can configure input rules to filter the columns that a dynamic port receives and to create generated ports for the filtered columns. Use input rules to propagate specific columns downstream in the mapping.
To process certain columns or certain type of columns in a dynamic port, define input rules to filter the columns based on a name, data type, pattern, or complex data type definition. For example, an employee database table has columns with decimal, string, and date/time data type. You need to process data only for columns that begin with SAL and of data type decimal. Create a dynamic port and define input rules to include only the columns that meet this condition.
Input Rule Configuration
Use the Input Rules dialog box to define the ports to include, to rename the generated ports, to change the order of the generated ports, and view the results of the rules.
The following image shows the Input Rules dialog box with a default input rule to include all ports from an upstream transformation:
When you configure input rules, you configure the following properties:
- Include or exclude ports
- Specify which ports to include or exclude in a dynamic port based on the port names or data type. You can define multiple rules. The Data Integration Service applies rules in the order in which they appear in the input rules list. Include all ports is the default input rule. Create at least one include input rule for a dynamic port.
- Include all remaining ports
- Add ports that are excluded from other dynamic ports in the transformation. When a transformation contains multiple dynamic ports, you can include all remaining ports from the upstream transformation in the last dynamic port.
- Rename generated ports
- Add a prefix or a suffix to the generated port names. Use prefixes or suffixes to indicate the transformation where ports are generated or to ensure that the port names are unique within each transformation.
- Restore source port names
- Restore the source port names in the generated ports. The Developer tool restores the port names as they appear in the nearest upstream transformation with static ports.
- Reorder generated ports
- Display generated ports according to the order of the rules that you enter. By default, the Developer tool displays the ports in the same order that they appear in the upstream transformation. You can also change the order according to the order of ports in the Read transformation. However, if one or more mid-stream transformations have dynamic ports and static ports, the Developer tool displays the ports in the order that they appear in the nearest upstream transformation with static ports.
After you configure the rules, you can preview the generated ports to verify the combination of rules. The Data Integration Service evaluates rules in the order that they appear in the Input Rules dialog box. You can change the order of the rules to ensure that they run in the correct order.
Include or Exclude Ports
You can include or exclude ports based on the port name or data type. Each input rule uses an operator and selection criteria to filter ports. You can define multiple rules. The Data Integration Service applies rules in the order in which they appear in the input rules list. Include all ports is the default input rule.
Configure the following input rule settings to determine which ports to include or exclude:
- Operator
- Determines whether to include or exclude ports. Default setting is to include ports.
- Selection Criteria
- Determines whether to filter ports based on port names or data types. When you choose the selection criteria, an input rule detail dialog box appears based on the criteria. For example, you provide the Name selection criteria details in the Input Rule Detail By Name List dialog box
- Detail
- Determines which ports to filter based on the details that you provide for port names or data type.
The following table describes the selection criteria and how to specify the details for the criteria:
Selection Criteria | Description | Criteria Details |
|---|
All | Includes all ports. Do not use this selection criteria with exclude operator. | You do not have to specify any details. |
Name | Filters ports based on the port name. | Select the port names from a list of values or use a parameter of type Port or Port List. You can also select the port names by source name. Note: Name values are not case sensitive. |
Type | Filters ports based on the data type of the port. | Select data types from a list. |
Pattern | Filters ports based on the pattern of the port name. | Choose prefix, suffix, or regular expression as the pattern type for the port name. Then, enter a value for the pattern or use a parameter of type String. You can also select the ports by source name to find the pattern in the source name. Note: Pattern values are not case sensitive. |
Complex Data Type Definition | Filters ports based on the complex data type definition. | Choose prefix, suffix, or regular expression as the pattern type for the complex data type definition. Then, enter a value for the pattern or use a parameter of type String. Note: Pattern values are not case sensitive. |
Include All Remaining Ports
When a transformation contains more than one dynamic port, you can configure the last dynamic port to include all ports that were not included in any other dynamic port.
For example, you want to remove the leading spaces from the string columns in a table and write the output of the string data along with the data for all other columns to the target. In an Expression transformation, you create two dynamic ports. You configure input rules to include all string data in one port and to put all remaining date in the other port. You choose the Include all remaining ports option for the last dynamic port.
The following image shows the dynamic ports String_Ports and Remaining_Ports in the Expression transformation:
Rename Generated Ports
You can generate unique port names through a setting that renames generated ports with a prefix or suffix.
For example, you can add an Agg_ prefix to indicate that the ports were generated in an Aggregator transformation.
The following image shows the renamed generated ports in the Aggregator transformation with an Agg_ prefix:
When you add ports of the same name to a transformation, the Developer tool appends a number to the generated port to resolve port naming conflicts. You might want to rename generated ports if the Data Integration Service will not be able to resolve port conflicts at run time. If the mapping uses a dynamic source, the Data Integration Service might encounter a port name conflict at run time. If the Data Integration Service encounters a port name conflict, it tries to rename the generated port. The mapping fails if the Data Integration Service cannot resolve the port name conflict. The mapping fails in the following situations:
- •An unresolved link exists from the renamed generated port to a static port.
- •A transformation property, such as group by port or join condition, uses the renamed generated port.
To avoid mapping failures, rename generated ports to ensure that the names are unique within each transformation.
Example - Rename Generated Ports
An Expression transformation has three dynamic ports. The "From_Read_Emp" and "From_Read_Emp2" dynamic ports include the generated port "Title." To avoid a name conflict, the Developer tool renames the generated port in "From_Read_Emp2" to "Title1."
The following image shows the renamed generated port Title1 in the Expression transformation:
You link the generated port "Title1" in the Expression transformation to a port "Title1" in the Sorter transformation. You also use "Title1" as the sort key.
The following image shows the link from the generated port in the Expression transformation to the port in the Sorter transformation:
If you add another link from the port "Title" in the Read transformation to a port "Title" in the Expression transformation, the Developer tool renames the generated ports. The generated port in the "From_Read_Emp" dynamic port is renamed to "Title1." The generated port in the "From_Read_Emp2" dynamic port is renamed to "Title2." The link to "Title1" in the Sorter transformation appears unresolved.
The following image shows the new link between the Read and Expression transformations, the generated ports that the Developer tool renamed in the Expression transformation, and the unresolved link to the Sorter transformation:
The mapping fails at run time because the generated port that is used as the sort key might not be the intended port to use.
To avoid mapping failure, rename generated ports to ensure that the names are unique within each transformation. For example, you want to trim the leading spaces in the string ports of the "From_Read_Emp" dynamic port. Add a suffix _trim to the generated ports. You want to find if the ports in the "From_Read_Emp2" dynamic port have null values. Add a suffix _isnull to the generated ports.
The following image shows the generated ports that you renamed in the Expression transformation:
Restore Source Port Names
You can restore source port names when you rename generated ports.
For example, you can rename generated ports to add a prefix Pre_ to all of the ports in one transformation in the mapping flow. In a downstream transformation, you can restore the source port names to restore the name before the prefix was added. When you restore the source port names, the Developer tool restores the port names as they appear in the nearest upstream transformation with static ports.
The following image shows an Expression transformation where the generated ports are renamed with a prefix Pre_:
The following image shows a Filter transformation where the source port names are restored:
The port names are restored according to the upstream Read transformation that contains static ports.
Reorder Generated Ports
You can reorder generated ports through a setting that reorders the ports based on the order of input rules or the ports in the Read transformation. By default, the Developer tool displays the generated ports in the same order that they appear in the upstream transformation.
You can choose one of the following options to reorder generated ports:
- Upstream group or dynamic port
- Display the ports in the same order that they appear in the group or the dynamic port of the upstream transformation. This is the default option.
- Input rules
- Display the generated ports based on the order of the input rules for the dynamic port.
The Data Integration Service reads rules in the order listed in the Input Rules dialog box. Review the port order and reorder them based on the order of the input rules. You can ensure that the Data Integration Service processes ports and rules in the order that you require. Reordering the ports also helps you view and analyze results.
- Nearest transformation with static ports
- Display the generated ports based on the order of the ports in the Read transformation.
Reordering ports based on this option helps you retain the original order of ports in the source. However, if one or more mid-stream transformations have dynamic ports and static ports, the Developer tool displays the ports in the order that they appear in the nearest upstream transformation with static ports. This option is valid only if the mapping has a single pipeline.
Example - Reorder Generated Ports
An employee flat file source has many columns that change frequently. You want to sort the employees by name and view the employee data in a way that the employee names appear in the first column followed by the city where the employees work. You also want to move the columns that are of type decimal to the end because you do not want to analyze the data for those columns.
The following image shows the dynamic port From_Read_EMPLOYEE with the original order of generated ports:
You configure the following input rules:
- •Include ports by "name" and "city."
- •Include all ports by string type.
- •Include all ports by decimal type.
Then, you choose to reorder the ports based on the input rule order. To verify the order, you can preview the port order.
The following image shows the order of the input rules:
The following image shows the reordered generated ports based on the input rules settings.
