Details about Active and passiveTransformations

Transformations

Active or passive tranformations: active change the number of rows, passive transformations keep the existing number of rows.

Connected transformations are connected to the dataflow and to other transformations. Unconnected transformations are called from within another transformation.

	name	Active	Passive	descr
	For all (or most) transformations			Most are connected transformations, some can be unconnected too. Most have two groups of ports: input and output.
	Aggregator	Active		Performs aggregate calculations. Define expression for the aggregate. Can use local variables (see below). Try to use pre-sorted data if possible. Aggregators do not sort, but use a clustering algorithm that appears to sort the output.
	Application Source Qualifier	Active		Represents the rows that the Integration Service reads from an application, such as an ERP source, when it runs a session.
	Custom	Active	Passive	Calls a procedure in a shared library or DLL.
	Expression		Passive	Calculates a value. Define the row-level calculation. Can use local variables (see below).
	External Procedure		Passive	Calls a procedure in a shared library or in the COM layer of Windows. Can be unconnected.
	Filter	Active		Filters data. Define the expression that returns true or false, with true indicating that the data can go through.
	HTTP Transformation		Passive	Connects to an HTTP server to read or update data. Contains input, output and header groups
	Mapplet Input		Passive	Defines mapplet input rows. Available in the Mapplet Designer.
	Java	Active	Passive	Executes user logic coded in Java. The byte code for the user logic is stored in the repository.
	Joiner	Active		Joins data from different databases or flat file systems. See more below.
	Lookup		Passive	Looks up values. See more below.
	Normalizer	Active		Source qualifier for COBOL sources. Can also use in the pipeline to normalize data from relational or flat file sources. For bringing in n columns into one, define one column as occurring n times. Link the n columns to the n input occurrences of the one column. This goes into one output column. The GCID_col goes from 1 to n and identifies which column was the source. The GK_col outputs a unique identifier, which is probably redundant with a primary key used for the target.
	Mapplet Output		Passive	Defines mapplet output rows. Available in the Mapplet Designer.
	Rank	Active		Limits records to a top or bottom range. Define expression for the ranking. Can use local variables (see below).
	Router	Active		Routes data into multiple transformations based on group conditions. One input group, multiple output groups, with one default group. Expressions returning true or false define the groups. A row for which all expression results are false goes to the default group. When linking, drag the input fields to get the names right, then drag the appropriate output fields to make the links.
	Sequence Generator		Passive	Generates primary keys. Normally, "start value" = 0, "Increase by" = 1, "current value" = 1, "reset" = no. To start at 1 at each execution, set "current value" to 1 and put check in "reset". Best practice is to make sequence generators reusable. Connect only the NEXTVAL port in mappings.
	Sorter	Active		Sorts data based on a sort key.
	Source Qualifier	Active		Represents the rows that the Integration Service reads from a relational or flat file source when it runs a session. Only ports that are linked to the next transformation are generated in the select statement. If fields do not show, it is because a link should be made with the next transformation. It is best to first generate the SQL before changing the "from" and "where" clauses so that the ports will line up with the fields of the SQL statement.
	SQL	Active	Passive	Executes SQL queries against a database.
	Stored Procedure		Passive	Calls a stored procedure. Can be unconnected. Best practice is to make stored procedure transformations reusable. See more information in the "basics" section.
	Transaction Control	Active		Defines commit and rollback transactions. An expression determines what type of transaction is performed (commit before or after, rollback before or after, or no change).
	Union	Active		Merges data from different databases or flat file systems. Multiple input groups, one output group.
	Update Strategy	Active		Determines whether to insert, delete, update, or reject rows. An expression returns a numerical value that determines which of the four strategies to use. Select "forward rejected rows" to pass the rejects to another transformation. Use variables in the "update strategy expression": DD_INSERT (0), DD_UPDATE (1), DD_DELETE (2), DD_REJECT (3). For updates, key is determined by the definition of the target, i.e. define the primary key in the target definition. Session level update strategy is defined by the session's "treat source rows as" property. The options insert, update, or delete act on all rows. The data driven option uses an update strategy transformation within the mapping. Only inserts or deletes or updates (mutually exclusive): "Treat Source Rows As" = insert or delete or update. Make sure to select insert/delete/update option for all target instances in the session. Different operations on rows: add an update strategy transformation. Make sure that the insert, delete, and one of the update options is selected for each target instance in the session. Select "data driven" for the "treat source rows as" property.
	XML Generator	Active		Reads data from one or more input ports and outputs XML through a single output port. Multiple input groups and one output group.
	XML Parser	Active		Reads XML from one input port and outputs data to one or more output ports. One input group and multiple output groups.
	XML Source Qualifier	Active		Represents the rows that the Integration Service reads from an XML source when it runs a session. Multiple input and output groups.
	XML Target Definition			Multiple input groups.