I want to automate the Data transfer process between 2 SQL tables using SSIS packages. The requirements are: 1. The Tables keep changing for every request 2. Both the Source & Destination are SQL Tables(of same name) in different servers. 3. The Destination tables may have referential constraints created,need to drop them before the data load,as it throws 'FK Constraint error'. 4. After the load, we need to recreate the dropped keys back in destination tables. Is this process automation possible using SSIS? If so can you brief me the logic?
What does it means to "automate the Data Transfer process" Do you need the two way sync like 1->2 and 2->1 or simply only push data from first instance to the second instance. Second question is why you are getting referential integrity errors. If you need to sync also the referenced tables between the two instances, then it means you are syncing the tables in wrong order. In case you do not need the data in referenced tables, then of course, this can be handled laso on SSIS. Basicaly you can achieve this in SSIS and checking of foreign key constraints can be easily disabled on the destiantion tables as you have the "Check Constraints" checkbox on the OLEDB Destination if you are using fast loads, which can be deselected and checking of constraints can be bypassed. But you have stated that the table is changing with every request. Then it depends on what frequency you need the synchronization to be performed. In case of only one way synchronization and quite frequent synchronization need, the easiest way to achieve the synchronization could be imlementing Transactional Replication for the needed tables. It would be nice to have more information about what is the purpose of the synchronization and what you would like to achieve. Definitelly you can achieven this using SSIS, but there may be a btter methods depending on the exact needs.