You can choose how Data Integration handles changes that you make to the schema of some data object types.
By default, if you make changes to the schema, Data Integration does not pick up the changes automatically. If you want Data Integration to refresh the data object schema every time the mapping task runs, you can enable dynamic schema handling.
A schema change includes one or more of the following changes to the data object:
•Fields are added.
•Fields are deleted.
•Fields are renamed.
•Field data type, precision, or scale is updated.
Data Integration automatically refreshes the schema for relational objects every time the task runs. If you want to dynamically refresh the schema for other object types, enable dynamic schema change handling in the Advanced Options area of the Runtime Options page when you configure the task.
The following table describes the schema change handling options:
Option
Description
Asynchronous
Default. Data Integration refreshes the schema when you edit the mapping or mapping task, and when Informatica Intelligent Cloud Services is upgraded.
Dynamic
Data Integration refreshes the schema every time the task runs.
Applicable for source, target, and lookup objects of certain connector types. For some connector types, Data Integration can only refresh the schema if the data object is a flat file.
If you select this option, the file object format must be delimited.
Not applicable to hierarchical data.
To see if a connector supports dynamic schema change handling, see the help for the appropriate connector.
If you update fields in the source object and you enable dynamic schema handling, be sure to update the Target transformation field mapping. Data Integration writes Null to the target fields that were previously mapped to the renamed or deleted source fields. If you use a target created at run time, update the target object name so that Data Integration creates a new target when the task runs. The task fails if Data Integration tries to alter a target created in a previous task run.
Dynamic schema handling options
When you enable dynamic schema change handling, you can select how Data Integration applies schema changes from upstream transformations to the target object. If the mapping contains more than one target, select the schema change handling for each target.
To select target schema options, the target field mapping must be automatic.
When you configure target schema options for objects that are created at runtime, Data Integration creates the target the first time you run the task. In subsequent task runs, Data Integration updates the target based on the schema change option that you select.
The schema change handling options available are based on the target connection. To see if a connector supports dynamic schema change handling, see the help for the appropriate connector.
The following table describes the schema handling options that you can select for each target type:
Schema handling option
Target type
Description
Keep Existing File Format
File
Data Integration fetches the most recent target schema at runtime and does not apply upstream schema changes to the target file.
Drop Current and Recreate
Database and file
For database targets, Data Integration drops the existing target table and creates a new target table with the schema from the upstream transformations on every run.
For file targets, Data Integration updates the target schema to match the incoming schema on every task run.
Alter and Apply Changes
Database
Data Integration updates the target schema with additive changes to match the schema from the upstream transformations. It does not delete columns from the target.
Don't Apply DDL Changes
Database
Data Integration fetches the target schema at runtime and does not apply upstream schema changes to the target table.
Data Integration does not pass field constraints to the target. For example, the source contains fields S1 and S2 configured with the NOT NULL constraint. The target contains fields T1 and T2 also configured with the NOT NULL constraint. You select the Alter and Apply Changes schema handling option. When you run the task, fields S1 and S2 are written to the target with no constraints.
Dynamic schema change handling rules and guidelines
Enable dynamic schema change handling so that Data Integration refreshes the data object schema every time the mapping task runs.
Consider the following rules and guidelines when you enable dynamic schema change handling:
•Changes to the object schema take precedence over changes to the field metadata in the mapping. For example, you add a field to the source object and then edit the metadata of an existing field in the mapping. At run time, Data Integration adds the new field and does not edit the existing field.
•Data Integration resolves parameters before picking up the object schema.
•Data Integration treats renamed fields as deleted and added columns. If you rename a field, you might need to update transformations that reference the renamed field. For example, if you rename a field that is used in the lookup condition, the lookup cannot find the new field and the task fails.
•When you rename, add, or delete fields, you might need to update the field mapping. For example, if you delete all the previously mapped fields in a target object, you must remap at least one field or the task fails.
•Data Integration writes Null values to a target field in the following situations:
- You rename a target field with automatic field mapping, and the field name does not match a source field.
- You rename a source field with manual field mapping, and you do not remap the field to the target.
•If you delete a field from a source or lookup object and a downstream transformation references the field, the task fails.
•If you change a source or lookup field type, the task might fail if the new field type results in errors downstream. For example, if you change an integer field in an arithmetic expression to a string field, the expression is not valid and the task fails.
•If you change a target field type, Data Integration converts the data from the incoming field to the new target field type. If the conversion results in an error, Data Integration drops the row. For example if you change a string type to a date type where the string does not contain a date, Data Integration drops the row.