Insert / Update Timestamp

[DS_SUPPORT]

If you know, whether the incoming row is Insert or update, then for the Insert Target Stage, Set target action as "Insert New Rows only" and for the Update Target Stage , set the target action as "Update Existing rows only". Because the target action "Insert or Update" will definetly reduce the performance.

And if you are doing the lookup only for getting the Created Timestamp, then the lookup is not necessary. Make the Created Timestamp as a Nullable field and dont pass any value to the Created Timestamp from the Job. Instead write an After Sql to update the Created Timestamp, some thing like

Code: Select all

Update YOURTABLE Set Created_TimeStamp = %DateTimeIn(#DSJobStartTimestamp#) where Created_TimeStamp IS NULL

In this case, your target action should be "Insert or Update", as you are not doing the lookup to determine , whether the Incoming row is Update or Insert.

[chulett]

Ugh... why insert all records and then update them afterwards to set the timestamp?

Set the timestamp in the transformer and use it in your DML. Biggest question to me is should each record have a 'current' timestamp, as in one that changes over the course of the job run? Or should each record in the unit of work have an identical timestamp?

Either is easy to setup. For the former, call for the system's date/time each time in the column's derivation. Or bind it into the DML itself and don't mention the column in the output stage. For the latter, create a stage variable and set it in the Initial Value area and then simply assign it to the field - it will get set once and remain constant while the job runs.