question

I would like to use SSIS -- not Transactional Replication -- to transfer data between various servers, where the data tables have Identity columns. I can manually set exclusive ID ranges on each server with DBCC CHECKIDENT and corresponding check constraints. And I can use SET IDENTITY_INSERT ON in SSIS to enable the ETL inserts. However, the ID range check constraints on the target have to be disabled during such inserts, and when the source ID is higher than the range set on the target server, the target will reseed automatically. I'm trying to avoid having to do something like the following for every table (with some strict transaction isolation level): BEGIN TRANSACTION SET @seed = IDENT_CURRENT('t1') ALTER TABLE t1 NOCHECK CONSTRAINT c1 SET IDENTITY_INSERT t1 ON -- DO THE INSERT(S) ALTER TABLE t1 CHECK CONSTRAINT c1 DBCC CHECKIDENT(t1, RESEED, @seed) COMMIT TRAN The "Not for Replication" property, when set on Identity columns and check constraints, produces the exact behavior I want (i.e. check constraints ignored on identity inserts and no reseed) Is there a "simple" way to invoke that behavior outside of replication?