You are here:

All Steps Tab

The All Steps tab allows you to apply certain conditions on all of the steps in a DTS file and to make changes to how data is committed.

Note: Features on the All Steps tab cannot be used with bulk mode steps. See Using Bulk Operations for more information.

Step conditions

The Step Conditions section allows you to specify some features when updating, inserting, or deleting records during a DTS run.

Note: Not all features are available for all connections. If the feature is not available for the selected connection, it is unavailable.

The Allow multirecord matches and Compare field on updates features are mutually exclusive. If one is selected, the other is always unavailable.

For bulk mode steps, these features are not available and Insight ignores the settings. They can be used in non-bulk steps within a DTS that has bulk mode steps.

The step conditions are:

Allow multirecord matches on updates/deletes -If checked, if Insight finds a match for more than one record during an update or delete, the operation is performed on all matching records. The following rules apply:
- If this feature is not selected (the default), and more than one match is found, the operation is not performed on any of the records, and an error is logged.
- This feature is only available if there are update and/or delete steps defined.
- For multitarget DTS files, all targets must allow multirecord updates.
Compare fields on updates (only update if changed) — If checked, Insight performs a field-by-field comparison of the source record to the matching target record before an update occurs. The following rules apply:
- This feature is only available if an update step is defined.
- If there are any differences between the records, the target record is updated. If all of the fields are the same, the update is not done, and it is considered a skip. Skips are included in the statistics for job status.
- The compare is only performed on columns for which the Overwrite option is selected.
Note: Compare fields on updates can significantly increase the amount of time required to run the job. Only use this feature when there is no other method to capture net changes in your source.

Commit Options

Step Result	Default Actions
Failure	Stop processing, rollback STEP, log error
Success (0)	Continue processing, commit STEP, no error
Success (1)	Continue processing, commit STEP, no error
Success (>1)	Stop processing, rollback STEP, log error

Option	Definition
Commit each step individually	Each step is committed to the database after being processed successfully.
Commit all steps together	All steps associated with processing one source row are committed to the database at the same time.
Commit repeating data together	All steps associated with processing a repeating group of source rows are committed to the database at the same time. If there is an error, all operations for all source rows in the current repeating group are recorded as failures, and previously executed transactions for the current repeating group are rolled back. Any other source rows in the repeating group are not processed. The application moves through the source data until it either finds a row that does not match the current repeating group, or until it hits the end of the source data. This option is only available if you are using the Skip if Repeated feature.
Commit specified number of source rows together	Select this option to commit a block of transactions at one time. You also must indicate the number of source rows to process. Committing every 100 source rows is the default and recommended interval. Committing larger blocks of transactions at one time can increase performance, depending on the environment. However, as with bulk operations, consider the result when setting the Source rows per transaction value.
Commit all rows together	Select this option to commit all source rows at one time. Generally, you would use this option when processing messages, rather than rows from a database or file. For example, an XML order with many line items should be processed with the Commit all rows together option enabled to avoid inserting a partial order.

The Commit all rows together and Commit repeating data together options are used to maintain data integrity at the row or repeating group level. These options ensure that incomplete transactions are not committed, when, for example, you use separate insert steps on the header data and its related detail data. If it is acceptable for a source row to be partially committed to a target, consider using Commit specified number of source rows to improve performance.

Note: The rollback option is not supported for Commit all rows together or Commit specified number of source rows together options. If an error occurs while processing the rows, it is counted as failed and written to the rejected row table (if that feature is enabled). All other rows processed are committed to the database.

Commit is not available when committing All Steps or Repeating Data. Data is committed when the source row or block of repeating data is completed successfully. You can not control how the data is committed for these two commit levels.

Step control can also be conditionally controlled based on the result of a formula.