Not sure if anyone cares about this or not, but I needed to know it to answer a question my boss had.
I downloaded a trial copy of Log Explorer from Lumigent to verify some things that were happening in the backend of a DTS package. Basically, many tables & views getting transferred to another DB on another machine. Since the old data is updatable, rather than run an Insert query and then an Update query, I elected to do a "Delete rows in Destination Table" on the transformation.
Apparently DTS treats the delete and the insert as a Bulk Copy type operation. When opening up a current version of the Transaction Log with Log Explorer, I found that every delete and insert done by DTS was recorded if the database's recovery model was set to FULL, but nothing from the operation was recorded if the database's recovery model was set to Bulk-Logged.
Additionally, (and obviously, but I had to test it to verify) Append to Table doesn't work if the source table contains records that the destination table already contains and there is a primary key constraint on the tables. Which indicates to me that Append to Table does not update previously existing records that might have changed even if there is no PK constraint.
Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!
I downloaded a trial copy of Log Explorer from Lumigent to verify some things that were happening in the backend of a DTS package. Basically, many tables & views getting transferred to another DB on another machine. Since the old data is updatable, rather than run an Insert query and then an Update query, I elected to do a "Delete rows in Destination Table" on the transformation.
Apparently DTS treats the delete and the insert as a Bulk Copy type operation. When opening up a current version of the Transaction Log with Log Explorer, I found that every delete and insert done by DTS was recorded if the database's recovery model was set to FULL, but nothing from the operation was recorded if the database's recovery model was set to Bulk-Logged.
Additionally, (and obviously, but I had to test it to verify) Append to Table doesn't work if the source table contains records that the destination table already contains and there is a primary key constraint on the tables. Which indicates to me that Append to Table does not update previously existing records that might have changed even if there is no PK constraint.
Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!
