SQLTeam.com | Weblogs | Forums

[Transfer SQL Server Objects] task

Hi, a newbie to this forum ..!
I have an existing SSIS 2012 package that picks up a collection of tables from a SQL Server 2016 database and transfers to a target SQL Server 2012, with no issues.

I wanted to replicate this, but changed to use a 2016 target database, but getting error stating I cannot transfer a system versioned table to that db. Is there a property I need to set on SSIS package or on the target database to allow me to use [Transfer SQL Server Objects] to transfer system versioned tables?

System versioning (temporal tables) feature was added to SQL Server 2016, it is not a recognized feature in SQL 2012, that's why your transfer works successfully.

You may be able to fix the SQL 2016 database by setting its compatibility level to 2014 or lower. If that does not work, the only way to transfer the data is to disable system versioning in the destination database. You'd have to script out the table definitions and apply them manually to the destination, the Transfer Table action in SSIS can't be used for this. You'd then use a normal data transform to copy data.

If you need more information on system versioning:

Many thanks Robert- that was very helpful!
You're a real people person, just like your name declares!