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 advanced mode, 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, 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.
To write to a fixed-width flat file type, select the fixed-width file format to use. If the list includes multiple fixed-width file formats with the same name, use the project and folder location that's appended to the name to determine the appropriate 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
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
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.
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. You cannot select Command for FTP/SFTP target connections.
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.
The following table describes the advanced properties when you write a message to a file target:
Property
Description
Input Type
Type of input to write to the target. Select one of the following options:
- Buffer
- File
The available options depend on the incoming data from upstream transformations. Default is buffer.
Acknowledgement Options
Options to save the acknowledgement. Select one of the following options:
- Save to session log to write the acknowledgement to the session log.
- Save to file to save the acknowledgement to a file.
- Discard to discard the acknowledgement.
Acknowledgement File Path
File path to write the acknowledgement if you save the acknowledgement to a file.
Operation if File Exists
Operation to perform if the acknowledgement file already exists. Select one of the following operations:
- Rename to rename the acknowledgement file.
- Append to append the acknowledgement to the file.
- Overwrite to overwrite the acknowledgement file.
Fail the job if acknowledgement is not AA/CA
Fails the job if the acknowledgement code is not AA or CA.
Retry the job if acknowledgement is AR/CR
Tries to run the job again if the acknowledgement code is AR or CR.
Message Retry Attempts
Number of attempts to try writing the message.
Message Retry Interval
Time interval in seconds to try writing the message again.
Validate Message
Indicates whether Data Integration validates the message.
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.
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.
Data Integration can write to a delimited or fixed-width target file. By default, when Data Integration creates a target at run time using a fixed-width file format, it rounds data that uses the Decimal or Double data types. You can choose to skip rounding the data for Decimal and Double data types. Data Integration doesn't round delimited data by default.
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.
6Optionally, if you're using a fixed-width flat file format that uses the Decimal or Double data type, and you don't want Data Integration to round the data, select Skip rounding decimal and double data types.
