Sql-server – How to sync two tables which are on different databases and servers using SSIS

As there are two servers there are no real good options. Pretty much your only choice will be to use triggers in both databases that write to the remote database. You'll need logic in the trigger so that the trigger does nothing if the call is coming from the other SQL instance. Otherwise you'll end up with a deadlock every time.

If one instance is down than nothing will work until that server is back up and running.

You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.

Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.

Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?

EDIT :

Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.

This utility provides a command line interface to: 1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table. The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.