At my company we have a need to migrate a portion of our provisional data between versions of our database, housed on different servers (upserts for the most part). For release management purposes, we have several environments, each containing a slightly different version of our database. The differences may include additional or missing: columns, constraints, tables, views, etc. Most importantly, we always have different data (identities representing the same entities may differ across environments as well).
The purpose of our migration requirement is that we'd like to be able to copy data back and forth between environments to stage production changes in our UAT environment, or to copy a particular data scenario/configuration back to our development environment for debugging or testing purposes.
I've read that SSIS is an option, but I'm not sure this applies to my scenario since we would like to leave existing similar (but unrelated) data in-tact. So far we've achieved our goal via custom migration scripts which use dynamically generated merge statements and by tracking identity values to maintain relationships. This approach is limited in capability and, without regular maintenance, it quickly falls out of date.
It seems our only viable alternative is spinning up a new SQL instance per configuration, which is an option not well-received by management (due to licensing and maintenance costs primarily).
Is there an easier/lower-maintenance way to transfer only a portion of our data dynamically between environments, while leaving similar but unrelated data in-tact? Or are we approaching the problem incorrectly?