Updating Or Deleting Source Rows During Processing
Depending on your connections, there are two ways to update source rows
using a DTS file:
Select a connection as both a source
and a target (or in the case of a text connection, add a source and a
target connection with the same text data object).
Use the Update/Delete Source Rows
dialog box to specify source row processing. The source table must have
a primary key defined.
Add a connection that can be used
as both a source and a target.
For text data sources, add two
connections to the same data. One can be selected as a source and the
second as a target.
Note: Not all
connections are valid for source and target connections. For example,
the same text connection cannot be both a source and a target.
From the Configure
Source dialog box, select and configure the connection and data object
you want to update.
From the Configure
Steps dialog box, select the same connection and data object.
Configure the connection as
desired, but be aware that any action you take on the target data for
this connection affects the source data.
Note: If you
use a data source as both the source and a target connection, be aware
that the target steps can affect the source data. If, for example, you
have a Delete step, when the operation deletes data in the target, the
data is also deleted in the source.
Using Update/Delete Source Rows
Click Update
Source in the Configure Source dialog box to define special actions
that accompany source row processing.
Select the operation you want
to perform:
Don't
modify source rows (default) -The source is never changed during
source row processing.
Update
source rows on success only — The source is updated when all of
the steps in a row are successfully processed. Use
this option when updating a row with a constant (such as "Success"
or "Processed").
Update
source rows on success or failure — The source is updated on either
success or failure. Use this option with the GETROWERROR function
to display errors for failed rows.
Specify the field to be updated and the corresponding
value to be stored when the integration condition occurs. The value can
be a literal, such as the text "Integration Failed", or a formula,
such as GETROWERROR
function, which returns the error message from the failed row or an empty
string for a successfully processed row.
Delete
source rows — The source row is deleted after it is successfully
processed. Use this option when you are working with a source in which
net changes are being tracked in a log file.
Select the field that
you want to update.
In the Value
pane, do one of the following:
Enter the value with which you
want to update the field (such as Success)
Click Formula
Editor to create a formula to determine how to update the source.
When you close the Formula Editor, the formula is copied to the Value
pane.
Click OK
to save your changes and close Update/Delete Source Rows.
Note: Some
adapters do not support this feature. Refer to the adapter documentation
for further information.
Update Source Example
Set Update Source to Update source
rows on success or failure. Select a field to be updated, such
as Status. In the Value
field, enter the function:
GETROWERROR()
When a row is successfully processed, the
GETROWERROR returns an empty string, causing the Status field to be updated
with an empty string.
When an error occurs, the GETROWERROR function
returns the corresponding error message, which is written to the Status
field.