DataFlux Data Management Studio 2.7: User Guide

Data Target (Insert) Node

You can add a Data Target (Insert) node to a data job to output data from your data job in a variety of data formats to a new data source (leaving your existing data as-is) or overwriting your existing data. Once you have added the node, you can double-click it to open its properties dialog. The properties dialog includes the following elements:

Name - Specifies a name for the node.

Notes - Enables you to open the Notes dialog. You use the dialog to enter optional details or any other relevant information for the node.

Output Table - Enables you to enter the path and name of your output file. You can also click Browse to access the Save As dialog to navigate to the output file location.

Options - Enables you to access the Options dialog, where you can configure options for commit intervals and inserting bulk data to a target.

Delete existing rows - Specifies that all existing records are deleted before results are added when data job results are written back to an existing table.

The Output fields section of the dialog includes the following elements:

Available - Displays the fields that you can make available for the next step in your data job. Items displayed in this list are dependent on your data sources and any preceding steps in your data job.

Selected - Displays the fields that will be made available to the next node in your data job.

Override - Displays the Override Defaults dialog. You can use the dialog to override the default field lengths.

You can access the following advanced properties by right-clicking the Data Target (Insert) node:

• BULK_ROW_COUNT (see Note 1)
• COMMIT_EVERY
• DELETE_ROWS
• DSN
• ENABLE_DDL
• FIELDS
• MAX_ERROR_COUNT
• POOL_NAME
• SCHEMA_NAME
• SQL_OVERRIDE (see Note 2)
• STATUS_FIELD
• TABLE_NAME

Note: 1) The BULK_ROW_COUNT property is supported by Oracle databases and some other databases. You can set this option through the Advanced Properties window or the Bulk Row Count field in the Data Target (Insert) node. If it is set, and an error is encountered in one of the rows in the bulk set, that row along with all the rows following it in that set will not be processed.

When using the Data Target (Insert) node to write SAS data sets, the default value for the BULK_ROW_COUNT option is 10,000. This setting is appropriate in most cases. However, the higher the setting, the more potential this option has to take up space in the transaction log. Be aware that on some systems, the default setting for SAS data sets might generate an “out of space” error for the transaction log.

Note: 2) The SQL_OVERRIDE property can be set only from the advanced properties dialog for the Data Target (Insert) data job node. Right-click the Fields property, and then select Edit Default Value. If you set the SQL_OVERRIDE property, then use the standard properties dialog to update any property of the Data Target (Insert) node, then the SQL_OVERRIDE setting will be lost.