Share via

Table replicate using SSIS

Sherwin Rodrigues 1 Reputation point
2020-08-31T06:54:58.88+00:00

​Hi,
I'm trying to replicate few tables from a Source DB to destination DB. Both Databases are sql server databases. The only issue is that the source DB gets refreshed from production DB backup every night which breaks its replication to the destination DB.
Is there a way I can achieve this table replication without it disconnecting due to the daily restore?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,485 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-01T02:15:44.873+00:00

    Hi Sherwin ,

    We can use Transfer SQL Server Objects Task in SSDT to replicate tables from Source DB to Destination DB.

    We can use SQL Agent job to schedule to run the ssis package before refreshing the Source DB.

    Please refer to the following links:

    1. Transfer SQL Server Objects Task in SSIS
    2. Deploy and Schedule an SQL Server Integration Services SSIS Package Step by Step

    Best Regards,
    Mona

    ----------

    If the response is helpful, please click "Accept Answer" and upvote it.