ETL - Tips on ETL develoment

When we do the development of mappings, workflows, there are some good practice, I will continuely list them in the following days,

Tips on mapping development
1) We can set the type the transformation to reusable, if we want reuse it. set the checkbox named Make reusable checked status.




2)Mapplet is one objects group with a standardized set of transformation logic. if some are used in your mapping, you can expand them in the Mapping Designer, so you can see all the objects(transformations) here, but need not open the mapplet in the mapplet designer.Click Mappings > Expand from the main menu. This expands the mapplet within the mapping for view. NOTE: but you cannot edit any of the properties.


3)one property of target table: Reject truncated and overflow data. (need to test to track it);
4)one property of target table: Table name prefix. (Need to test if it wants to format the table name like cmst.tablename).

ETL - Tips on propagating port attributes

It is a good way for us to propagate the other transformations' related ports when we give some changes to one transformation's port(such as the port name, datatype, precision, scale, and description...).

But please note, The Designer does not propagate changes to the following mapping objects:
1)Unconnected transformations;
2)Reusable transformations;
3)Mapplets;
4)Source and target instances;
5)SDK Source Qualifier;

and if you want to get more inforamtion about propagating port attributes, please refer to the bookshelf.

ETL - Tips on Linking Ports

When you develop the mapping, if you want link the ports of two transformations, You can manually link ports, or you can automatically link ports between transformations. When you link ports automatically, you can link by position or by name. I think it is easy to understand the manual way; here I just list the difference between automatica linking by position and name;

1) Automatic Linking by Position
When you link by position, the Designer links the first output port to the first input port of the another transformation, the second output port to the second input port, and so on. so the ports will be kept in the same order for the two transformations.
2) Automatic Linking by Name
Another way, You can link ports by name in the Designer. The Designer adds links between input and output ports that have the same name. Linking by name is not case-sensitive. Link by name when you use the same port names across transformations. The Designer can link ports based on prefixes and suffixes you define. Link by name and prefix or suffix when you use prefixes or suffixes in port names to distinguish where they occur in the mapping or mapplet.

you can Click Layout > Autolink in mapping or mapplet window to get more infomation.

ETL - Rules and Guidelines for Connecting Mapping Objects

If the Designer detects an error when you try to link ports between two mapping objects, it displays a symbol indicating that you cannot link the ports.Use the following rules and guidelines when you connect mapping objects:

♦Follow logic of data flow in the mapping. Given the logic of the data flow between sources and targets, you can link the following types of ports:−The receiving port must be an input or input/output port.−The originating port must be an output or input/output port.−You cannot link input ports to input ports or output ports to output ports.

♦You must link at least one port of an input group to an upstream transformation.

♦You must link at least one port of an output group to a downstream transformation.

♦You can link ports from one active transformation or one output group of an active transformation to an input group of another transformation.

♦You cannot connect an active transformation and a passive transformation to the same downstream transformation or transformation input group.

♦You cannot connect more than one active transformation to the same downstream transformation or transformation input group.

♦You can connect any number of passive transformations to the same downstream transformation, transformation input group, or target.

♦You can link ports from two output groups in the same transformation to one Joiner transformation configured for sorted data as long as the data from both output groups is sorted.

♦You can only link ports with compatible datatypes. The Designer verifies that it can map between the two datatypes before linking them. The Integration Service cannot transform data between ports with incompatible datatypes. While the datatypes do not have to be identical, they do have to be compatible, such as Char and Varchar.

♦ You must connect a source definition to a source qualifier only. You then link the source qualifier to targets or other transformations.

♦You can link columns to a target definition in a mapping, but you cannot copy columns into a target definition in a mapping. Use the Target Designer to add columns to a target definition.Note: Even if you follow the rules and guidelines listed here, the Designer marks some mappings invalid if the mapping violates data flow validation.

ETL - Tips on SQL Qualifier transaction

...