I have two databases: A and B. I have read only access to Database A, full control on Database B. They are on the same server. Database A is rebuild every night.
Using BIML Express all schemas and tables are copied from database A into database B.
Existing tables in database B are overwritten by the corresponding ones in database A. Objects in database B that do not exist in Database A are left untouched. For policy reasons we cannot query Database A, except for copying the contents once in the morning.
Using BIML Express we created a SSIS package doing the dropping, creating and filling of the tables. Whenever something changed in Database A we would be informed, run the BIML scripts and thus generate a new package incorporating the changes.
However, we no longer can use BIML since we are migrating company wide to Visual Studio 2022. We have to adapt the SSIS package manually from now on whenever there is a change in database A. Since changes are fairly frequent this is a lot of work.
Is there another way of doing this automatically? Since database A is rebuild every night I see hurdles using replication.
The server at the moment is SQL Server 2016, will be upgraded in the coming weeks to 2019.
Just to be clear, I am in no way beholden to Visual Studio, SSIS, BIML. Just looking for a solution with no extra price tag since this is temporary.