Timestamp on SSIS update on tsql

Question

I have a couple of SSIS packages that copy the content of several tables to another db. One of the columns updated in the process is a timestamp datatype. From what I read this datatype can't be updated, it gets updated automatically with each UPDATE or INSERT. But digging on the net I've found this and this about issues on SSIS when updating this type of column and using DT_BYTES and DT_WSTR types. If I got it right, using SSIS packages those columns can be modified.

My issue is that now I want to replace those SSIS packages with stored procedures that will be called by the SQL Server Agent. I'm reusing the already existing code on the packages but of course I'm getting the errors "Cannot insert an explicit value into a timestamp column." or "Cannot update a timestamp column.".

Is there any way I can replicate the timestamp values from one database to the other? Without changing the table structure or column type as the whole process is already configured and running on several databases.

Answer 1

I know of two ways to copy timestamp data:

• A SELECT..INTO will insert explicit values into a timestamp column, but it also requires that the destination table does not exist. That will not help in your situation.
• Convert it to a VARBINARY or VARCHAR value. This is the equivalent of the SSIS conversions to DT_BYTES/DT_WSTR you mentioned. In both cases, you can copy the values, you can manipulate the values, but you cannot reinsert them into a TIMESTAMP column.

In short, no, I don't know of a way to copy the exact same timestamp values between databases. If you need the exact value, you'd need to change the column type on the destination.

In most cases I've seen, though, the value is much less important than the sequence provided by the timestamp column. If you insert the values into the destination table in the order of the timestamp values in the source table, you should end up with the same sequence, even if the values are different.