When you configure a file target, you specify the connection, target type, and target object.
For flat file targets, you can select an existing target object or create a new target at run time.
If you create a flat file target at run time, you can specify a static or dynamic file name.
File target properties
You configure file target properties on the Target tab of the Properties panel.
The following table describes the file target details:
Property
Description
Connection
Name of the target connection.
Target Type
Target type, either single object or parameter.
Object
Name of the target object.
Formatting Options
Flat file format options. Opens the Formatting Options dialog box to define the format of the file.
Select Delimited. Fixed-width files are not used in Data Integration.
To write to a delimited flat file type, configure the following file format options:
- Delimiter. Delimiter character. Can be a comma, tab character, colon, semicolon, nonprintable control character, or a single-byte or multibyte character that you specify.
- Treat multiple characters as a single delimiter. Treats the specified set of delimiters as one delimiter. For example, a source file contains the following record: abc~def|ghi~|~|jkl|~mno. If you specify the delimiter as ( ~ | ), Data Integration reads the record as three columns separated by two delimiters: abc~def|ghi, NULL, jkl|~mno. If you disable this option, Data Integration reads the record as nine columns separated by eight delimiters: abc, def, ghi, NULL, NULL, NULL, jkl, NULL, mno.
- Treat consecutive delimiters as one. Treats one or more consecutive column delimiters as one. The default is to treat consecutive delimiters as a null value.
- Row Delimiter. Line break character. Select a line break character from the list. The default is line-feed, \012 LF.
Note: Linux machines write "\n" as the line-feed character. Windows machines write "\r\n" as the line-feed character.
- Text Qualifier. Character to qualify text.
- Escape character. Escape character.
- Field labels. Determines if the mapping task generates field labels or imports labels from the source file.
- First data row. The first row of data. The task starts the read at the row number that you enter.
You can use a tab, space, or any printable special character as a delimiter. The delimiter can have a maximum of 10 characters. The delimiter must be different from the escape character and text qualifier.
Note: Use caustion when updating the file formating options after you have updated the target field metadata. Changes to the formating options can synchronize fields with fields in the target object. For more information see Target fields.
Operation
For file targets, the operation is always Insert.
The following table describes the advanced properties for flat file targets:
Property
Description
Forward Rejected Rows
Indicates whether the mapping task forwards rejected rows to the reject file.
If you enable row error handling, the mapping task writes the rejected rows and the dropped rows to the row error logs instead of generating a reject file. If you want to write the dropped rows to the session log in addition to the row error logs, you can enable verbose data tracing.
If you don't forward rejected rows, the mapping task drops rejected rows and writes them to the session log.
Thousand Separator
Thousand separator character. Can be none, comma, or period. Cannot be the same as the decimal separator or the delimiter character.
Field type must be Number. You might also need to update the field precision and scale.
Default is None.
Decimal Separator
Decimal character. Can be a comma or period. Cannot be the same as the thousand separator or delimiter character.
Field type must be Number. You might also need to update the field precision and scale.
Default is Period.
Datetime Format
Optional. Overrides the datetime format specified in the flat file connection.
Enter the datetimeformat to override.
For example, YYYYMMDD.
Append if Exists
This property is not used.
Create Target Directory
Creates the target directory if it doesn't exist as specified in the Output file directory field.
Header Options
Creates a header row in the file target. You can choose the following options:
- No Header. Do not create a header row in the flat file target.
- Output Field Names. Create a header row in the file target with the output field names.
- Use header command output. Use the command in the Header Command field to generate a header row. For example, you can use a command to add the date to a header row for the file target.
Default is No Header.
Header Command
Command used to generate the header row in the file target. For example, you can use a command to add the date to a header row for the file target.
Footer Command
Command used to generate the footer row in the file target.
Output Type
Type of target for the task. Select File to write the target data to a file target. Select Command to output data to a command.
Output File Name
File name or file name and path of the output file. By default, the mapping task names output files after the target object.
Target objects must use one of the following file extensions: csv, dat, txt
Output File Directory
Name of the output directory for a flat file target. By default, the mapping task writes output files to the target connection directory.
You can also use an input parameter to specify the target file directory.
If you use the service process variable directory $PMTargetFileDir, the task writes target files to the configured path for the system variable. To find the configured path of a system variable, see the pmrdtm.cfg file located at the following directory:
<Secure Agent installation directory>\apps\Data_Integration_Server\<Data Integration Server version>\ICS\main\bin\rdtm
You can also find the configured path for the $PMTargetFileDir variable in the Data Integration Server system configuration details in Administrator.
Reject File Directory
Directory path to write the reject file. By default, the mapping task writes all reject files to the following service process variable directory:
$PMBadFileDir/<federated task ID>
If you specify both the directory and file name in the Reject File Name field, clear this field. The mapping task concatenates this field with the Reject File Name field when it runs the task.
Reject File Name
File name, or file name and path of the reject file. By default, the mapping task names the reject file after the target object name: <target name>.bad.
The mapping task concatenates this field with the Reject File Directory field when it runs the task. For example, if you have C:\reject_file\ in the Reject File Directory field, and enter filename.bad in the Reject File Name field, the mapping task writes rejected rows to C:\reject_file\filename.bad.
Flat file targets created at run time
If a mapping includes a flat file target, you can select an existing target file or create the target at run time. When you create a target at run time, Data Integration automatically discovers the target object metadata for data type, precision, and scale, based on the data source.
If you need to edit target object metadata, you can edit it in the Source transformation.
You cannot link the target fields to the upstream transformation. If you want to reduce the number of unused fields in the target, configure field rules in the Target transformation or in the upstream transformations.
When you create a flat file target at run time, the mapping task creates the physical target the first time the mapping runs based on the fields from the upstream transformation. In subsequent runs, if the target file name does not change, the mapping task overwrites the target file. If the file name changes between mapping runs, the mapping task creates a new target. Data Integration creates the target in the default connection directory.
You can configure a static or dynamic file name for the target file. A static file name can include a time stamp. A dynamic file name uses an expression to generate the file name when the mapping task runs.
Flat file targets with static file names
If you create a flat file target at run time, you can specify a static file name. The file name can include a time stamp that indicates when the file was created.
To specify a static file name, in the Target Object dialog box, enter the file name in the Static File Name field. To include a time stamp, enable Handle Special Characters and add the time stamp characters to the file name. For example, the file name MyTarget_%d-%m.csv includes the day and month in which the mapping ran.
The following image shows the Target Object dialog box:
If you do not include a time stamp, the mapping task creates the target file the first time the task runs and overwrites the file during subsequent runs.
If you append a time stamp to the target file name, the mapping task writes data to a new file when the time stamp changes. For example, you enable special character handling, enter static file name MyTarget_%d-%m.csv, and run the mapping task on January 15 and January 16. The mapping task creates the target files MyTarget_15-01.csv and MyTarget_16-01.csv.
Flat file target time stamps
When you create a flat file target at run time, you can append time stamp information to the file name to show when the file is created.
When you specify the file name for the target file, you include special characters based on Linux STRFTIME function formats that the mapping task uses to include the time stamp information in the file name. The time stamp is based on the organization's time zone.
The following table describes some common STRFTIME function formats that you might use:
Special Character
Description
%d
Day as a two-decimal number, with a range of 01-31.
%m
Month as a two-decimal number, with a range of 01-12.
%y
Year as a two-decimal number without the century, with range of 00-99.
%Y
Year including the century, for example 2015.
%T
Time in 24-hour notation, equivalent to %H:%:M:%S.
%H
Hour in 24-hour clock notation, with a range of 00-24.
%l
Hour in 12-hour clock notation, with a range of 01-12.
%M
Minute as a decimal, with a range of 00-59.
%S
Second as a decimal, with a range of 00-60.
%p
Either AM or PM.
Flat file targets with dynamic file names
If you create a flat file target at run time, you can specify a dynamic file name. A dynamic file name uses an expression to generate the file name.
You can use a dynamic file name to create a new target file every time the mapping task runs. For example, the following expression creates a file called "OrdersOut_<system_timestamp_with_second_precision>.csv" each time the mapping task runs:
You can also use a dynamic file name in a mapping that contains a Transaction Control transformation to write data to a different target file each time a transaction boundary changes. For example, the following expression can be used in a target that is downstream of the Transaction Control transformation to commit data to a different target file every time the DEPT_ID field changes:
'Results_Dept_'||To_Char(DEPT_ID)||'.dat'
To specify a dynamic file name, in the Target Object dialog box, select Use a Dynamic File Name and enter the file name expression in the expression editor.
The following image shows the Target Object dialog box with the Use a Dynamic File Name option enabled:
You can include incoming field names, constants, operators, built-in functions, and user-defined functions in the target file name expression.
If you use an incoming field name in the file name expression, you can choose to exclude the field from the target. When you enable the Exclude Dynamic File Name Field option, Data Integration does not write the incoming field used in the expression to the target. Include only one incoming field in the expression. If you include more than one incoming field, the expression is invalid.
To use more than one incoming field, add an Expression transformation directly before the Target transformation. In the Expression transformation, configure a field to hold the expression that you want to use as the file name. In the Target transformation, use this field as the expression in the dynamic file name.
For more information about creating expressions, see the Function Reference.
Creating a flat file target at run time
To create a flat file target at run time, select Create New at Runtime in the Target Object dialog box. Then configure a static or dynamic file name for the target file.
1On the Target tab of the Target transformation, select a flat file connection.
2Set the target type to Single Object.
3Click Select to select the target object.
4In the Target Object dialog box, select Create New at Runtime.
5 Configure the target file name:
- To enter a static file name without a time stamp, enter the file name in the Static File Name field.
- To enter a static file name that contains a time stamp, enable Handle Special Characters and enter the file name, including the time stamp characters, in the Static File Name field.
- To enter a dynamic file name, enable Use a Dynamic File Name and configure the expression to create the file name.
You can use fields, system variables, parameters, built-in functions, and user-defined functions in the file name expression.
