Generate Column Metadata from Control Files
Control files are simple text files based on which you can create flat file data objects. You can also use control files to update columns at run time for a Read transformation based on a flat file data object.
When the Data Integration Service generates columns at run time, it uses the decimal and thousands separators specified in the flat file data object properties. You must specify only the name of the column in the control file if the column exists in the flat file data object.
When you specify the source type as indirect in the run-time properties in the Advanced view of the flat file source, you can generate column names at run time with the control file.
You can create a control file for a delimited or for a fixed-width flat file.
You can parameterize the control file name and the control file directory in the run-time properties of the flat file data object.
The Data Integration Service processes a control file based on the column metadata that you specify in the control file and the input data.
When you generate column names from a control file at run time and the source data object contains the file name column, the Data Integration Service logs a validation error with the duplicate file names.
Control File Formats
Delimited and fixed-width flat files have different control file formats. You can use control files to generate column metadata at run time or to create a flat file data object.
Use one of the following formats to create a control file:
- Delimited control file
Each line in a delimited control file has the following format:
[column name],<data type>,<precision>,<scale>
The following example shows a delimited flat file control file:
Dept,String,10
Name
Place,String,20
Country
Phone number,string,30
- Fixed-width control file
Each line in a fixed-width control file has the following format:
[column name],<data type>,<precision>,<scale>,<number of bytes to process>
The following example shows a delimited flat file control file:
Dept,String,10
Name
Place,String,20
Country
Phone number,string,30
For a fixed-width source, the control file contains an additional column for the number of bytes to process. If you do not specify the number of bytes to process, the Data Integration Service uses the value specified for the precision as the number of bytes to process.
Parameterization of Run-time Properties
You can parameterize or specify values for the default scale, precision, and data type of the additional columns from the control file in the run-time properties of the flat file data object. You can parameterize the control file name and the control file directory in the run-time properties of the flat file data object. Configure the parameters on the Advanced tab of the physical data object properties. When you create the transformation from the physical data object, you can use mapping parameters to override the parameter default values.
Run-time Processing of Control Files
When you develop a mapping, you define data object read properties that determine how data is read from a flat file. The Data Integration Service can process the columns of the flat file data object that is based on a control file.
When the Data Integration Service constructs the column metadata based on the control file, it applies the following criteria for processing data:
- The Data Integration Service applies default values for the column properties that you do not specify for the flat file data object
- The Data Integration Service applies the default run-time properties of the flat file data object when you do not specify the run-time column properties in the control file. When you have additional columns in the control file without a data type, precision, or scale, the Data Integration Service uses the default data type, precision, and scale.
- The Data Integration Service processes data as NULL for columns that do not appear in the control file but are present in the flat file data object
- When the column in the flat file data object does not exist in the control file, the Data Integration Service processes the data as NULL during data preview. During run time, the Data Integration Service cannot process the column as that column does not exist in the control file.
- The Data Integration Service fails mapping when the source metadata mismatches with a control file
- The Data Integration Service fails to process the data when the source metadata based on the control file does not match the input data. For example, the Data Integration Service fails the mapping when the source contains a data type in the control file that is not compatible with the data type in the source.
Rules and Guidelines for Control Files
Consider the following guidelines when you use a control file:
- •The control file must be accessible to the machine where the Data Integration Service runs.
- •After you import a control file as a flat file data object, you cannot change the data type, precision, and scale of the columns in the data object. You can change the column position and column metadata for the additional columns from the control file.
- •When you generate column names at run time based on the control file, the lookup cache reflects the metadata in the control file. If you update the same control file with additional columns after you run the mapping once, you must select the Recache from Lookup source property from the Runtime properties of the Lookup transformation.
- •You cannot specify date format columns in the control file. The Data Integration Service uses the flat file date format from the flat file data object advanced properties in the Advanced view. If the flat file source contains multiple date formats, the Data Integration Service will use only the one date format specified in the Advanced view for all the dates.
- •You cannot specify a binary data type in the control file. If the control file contains a TimestampwithTZ data type, ensure that the data type has the precision set to 36 and the scale set to 9 in the control file.