Transformations > Target transformation > File targets
  

File targets

File targets include flat files and FTP/SFTP files. When you configure a file target, you specify the connection, target type, and target object.
For FTP/SFTP targets, you can select an existing 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.
In an elastic mapping, the object name cannot contain the dollar sign character, $. The dollar sign is a reserved character for parameters.
Formatting Options
Flat file format options. Opens the Formatting Options dialog box to define the format of the file.
You can choose either a delimited or fixed-width file type. Default is delimited.
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, end of transmission (EOT) character, or a character that you specify.
  • - 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.
To write to a fixed-width flat file type, select the fixed-width file format to use. If you do not have a fixed-width file format, click New > Components > Fixed Width File Format to create one.
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
Causes the mapping task to forward rejected rows to the reject file.
If you do not forward rejected rows, the mapping task drops rejected rows and writes them to the session log.
If you enable row error handling, the mapping task writes the rejected rows and the dropped rows to the row error logs. It does not generate 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.
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.
Append if Exists
Appends the output data to the target files and reject files for each partition. You cannot use this option for FTP/SFTP target files.
If you do not select this option, the mapping task truncates each target file before writing the output data to the target file. If the file does not exist, the mapping task creates it.
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. You cannot select Command for FTP/SFTP target connections.
Output File Directory
Name of output directory for a flat file target. By default, the mapping task writes output files in the service process variable directory, $PMTargetFileDir.
You can also use the $OutputFileName session parameter to specify the file directory.
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/<task federated 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.
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:
The image shows the Target Object dialog box for a flat file target. The "Create New at Runtime" option is selected and the "Use a Dynamic File Name" check box is not checked. Because a dynamic file name is not selected, the Static File Name field appears at the bottom of the dialog box. The name "MyTarget.csv" is entered in the Static File Name field.
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:
'OrdersOut_'||To_Char(SYSDATE, 'YYYYMMDDHH24MISS')||'.csv'
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:
The image shows the Target Object dialog box for a flat file target. The "Create New at Runtime" option is selected and the "Use a Dynamic File Name" check box is checked. Because a dynamic file name is selected, the expression editor appears in the dialog box. The expression 'Results_Dept_'||To_Char(DEPT_ID)||'.dat' is entered in the expression editor.
You can include incoming field names, constants, operators, built-in functions, and user-defined functions in the target file name expression.
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.
    1. On the Target tab of the Target transformation, select a flat file connection.
    2. Set the target type to Single Object.
    3. Click Select to select the target object.
    4. In the Target Object dialog box, select Create New at Runtime.
    5. Configure the target file name:
    6. Click OK.