Write Transformation Properties
After you create a Write transformation, you can configure properties for the transformation.
Configure Write transformation properties on tabs in the Properties view of the transformation. The tabs that you can configure depend on the type of target that the Write transformation represents.
The following table describes each property tab and identifies the target type that you use the tab for:
Property Tab | Description | Target Type |
|---|
General | Specify transformation properties and behavior. For relational and customized data object sources, synchronize transformation input ports with source. | All |
Data Object | Specify the transformation data source. For relational and customized data object sources, get data object columns from data source at run time. | Flat file Relational Customized data object |
Format | Input settings for a flat file data source | Flat file |
Ports | Set port definition by the associated data object or by mapping flow. | All |
Run-time | Properties that the Data Integration Service uses when writing data to the target at run time, such as where to send reject files. For a flat file target, reject file names and directories. | Flat file Relational |
Data Object Parameters | View the data object parameters. Configure parameter values for the mapping or bind the parameters to a mapping parameters. | Flat file Customized data object Logical data objects |
Run-time Linking | Create new run-time links and view link properties. | All |
Advanced | Set tracing level and row order. For a relational target, create or replace the target table at run time. | Flat file Relational Customized data objects Logical data objects |
General Properties
You can configure the name and description of the Write transformation. You can also configure the following properties:
- When Column Metadata Changes
- Available for relational and customized targets. Select one of the following options:
- - Synchronize input ports. The Developer tool updates Write transformation input ports with metadata changes that the Model repository stores for the data object.
- - Do not synchronize. The Developer tool does not show metadata changes in the data object.
- Default is the Synchronize input ports option.
- Physical Data Object
- Available for flat file and customized targets. The object used to create the transformation.
- You can select the data object name and configure its properties.
Data Object Properties
On the Data Object tab, you can specify or change the Write transformation target, and make relational and customized data object targets dynamic.
You can configure the following properties:
- Specify By
- To specify target columns and metadata for the Write transformation, select one of the following options:
- - Value. The Write transformation uses the associated data object to specify target columns and metadata.
- - Parameter. The Write transformation uses a parameter to specify target columns and metadata.
- Default is the Value option.
- Data Object
- If you created the Write transformation from an existing data object, the field displays the name of the object. Click Browse to change the data object to associate with the Write transformation.
- Parameter
- Choose or create a parameter to associate with the Write transformation.
- At run time, get data object columns from the data source
- When you enable this option, the Data Integration Service fetches metadata and data definition changes from target tables to the Write transformation.
- To preview how the Data Integration Service fetches metadata and data definition changes, view the mapping with resolved parameters.
Ports Properties
On the Ports tab, you can configure the following properties:
- Columns defined by
- Select one of the following options to define Write transformation columns:
- - Associated data object. Use column names, metadata, and other properties from the data object in the Data Object tab.
- - Mapping flow. The mapping fetches column names, metadata, and other properties from the upstream objects in the mapping.
- Default is the Associated data object option.
- Column resource properties
- Available for flat file and customized targets. The resource for each column is the data object from which the column fetches its name, metadata, and other properties. Select the resource name to change resource properties.
Run-Time Properties
You can configure the following Write transaction properties on the Run-time tab:
- Connection
- Available for relational targets. Connection used by the transformation. Click in the right side of the field to change the connection.
- The following image shows the location of the dropdown button to click:
- Reject truncated/overflow rows
- Available for relational and customized targets.
- The Developer tool lets you convert data by passing it from port to port. Sometimes a conversion causes an overflow of numeric data or truncation of strings in columns that contain characters. For example, passing data from a Decimal (28, 2) to a Decimal (19, 2) port causes a numeric overflow. Likewise, if you pass data from a String(28) port to a String(10) port, the Data Integration Service truncates the strings to 10 characters.
- When a conversion causes an overflow, the Data Integration Service, by default, skips the row. The Data Integration Service does not write the data to the reject file. For strings, the Data Integration Service truncates the string and passes it to the next transformation.
- Select this option to include all truncated and overflow data between the last transformation and target in the session reject file. The Data Integration Service sends all truncated rows and any overflow rows to the session reject file or to the row error logs, depending on how you configure the session.
- Reject file directory
- Directory where the reject file exists. Default is the RejectDir system parameter.
- Reject file name
- File name of the reject file. Default is <output_file_name>.bad.
- If multiple partitions write to the flat file target, each partition writes to a separate reject file named <output_file_name><partition_number>.bad.
Run-time Linking Properties
Create and configure a run-time link on the Run-time Linking tab. A run-time link is a group-to-group link between transformations that uses a parameter, a link policy, or both to determine which ports to link at run time. Run-time links appear as thick lines in the mapping editor.
Create and configure run-time links to a Write transformation in the following cases:
- •The target data object in the Write transformation uses a parameter.
- •Ports from the upstream transformation can change at run time.
Note: Do not create a run-time link to a Write transformation when you define the target columns based on the mapping flow.
You can perform the following tasks on the Run-time Linking tab:
- Create a run-time link.
In the Links area, click the New button and select the transformation from which you want to link the ports to the Write transformation at run time in the New Link dialog box.
- Configure the run-time link properties.
In the Link Properties area, configure the following run-time link properties:
- Parameter
- Select this option when the port names can change between mapping runs and you know the port name values. Use a parameter of type Input Link Set to connect ports by name values between mapping runs. The syntax for the Input Link Set mapping parameter consists of comma-separated pairs of ports: Afield1->Bfield2, Afield3->Bfield4 .
- Link Policy
- Select this option when you want to automatically link ports that have matching names. For example, when both of the mapping objects contain a port called SALARY, the Data Integration Service links them. You can ignore prefixes and suffixes in the port names.
At run time, the Data Integration Service establishes and resolves links between the ports in the following order:
- •Links that you manually create in the mapping editor.
- •Links based on the parameter that you configured for a run-time link.
- •Links based on the link policy that you configured for a run-time link.
For more information on run-time links, see the Informatica Developer Mapping Guide.
Advanced Properties
Configure advanced properties to determine how the Data Integration Service processes data for the Write transformation.
Configure the following properties on the Advanced tab:
- Tracing level
- Control the amount of detail in the mapping log file.
- Target load type
- Type of target loading. Select Normal or Bulk. You can set the target load type for relational resources or customized data objects.
If you select Normal, the Data Integration Service loads targets normally. You can choose Bulk when you load to DB2, Sybase, Oracle, or Microsoft SQL Server. If you specify Bulk for other database types, the Data Integration Service reverts to a normal load. Bulk loading can increase mapping performance, but it limits the ability to recover because no database logging occurs.
Choose Normal mode if the mapping contains an Update Strategy transformation. If you choose Normal and the Microsoft SQL Server target name includes spaces, configure the following environment SQL in the connection object:
SET QUOTED_IDENTIFIER ON
- Update override
- Overrides the default UPDATE statement for the target.
- Delete
- Deletes all rows flagged for delete.
- Default is enabled.
- Insert
- Inserts all rows flagged for insert.
- Default is enabled.
- Create or replace table at run time
The Data Integration Service drops the target table at run time and replaces it with a table based on a target table that you identify.
- Truncate target table
- Truncates the target before it loads data.
- Default is enabled.
- Truncate target partition
- Truncates an internal or external partitioned Hive target before it loads data. You must choose Truncate target table before you choose this option.
- Default is disabled.
- Update strategy
- Update strategy for existing rows. You can select one of the following strategies:
- - Update as update. The Data Integration Service updates all rows flagged for update.
- - Update as insert. The Data Integration Service inserts all rows flagged for update. You must also select the Insert target option.
- - Update else insert. The Data Integration Service updates rows flagged for update if they exist in the target and then inserts any remaining rows marked for insert. You must also select the Insert target option.
- PreSQL
- SQL command the Data Integration Service runs against the target database before it reads the source.
- The Developer tool does not validate the SQL.
- PostSQL
- SQL command that the Data Integration Service runs against the target database after it writes to the target.
- The Developer tool does not validate the SQL.
- Maintain row order
- Maintain the row order of the input data to the target. Select this option if the Data Integration Service should not perform any optimization that can change the row order.
- When the Data Integration Service performs optimizations, it might lose the row order that was established earlier in the mapping. You can establish row order in a mapping with a sorted flat file source, a sorted relational source, or a Sorter transformation. When you configure a target to maintain row order, the Data Integration Service does not perform optimizations for the target.
- Constraints
- SQL statements for table-level referential integrity constraints. Applies to relational targets only.
