asked
Automation of SQL tables data transfer using SSIS
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?
With respect to points 3/4 are you saying that the correct data is in the load, but that the foreign tables aren't loaded before the referencing tables are? Or that the values won't exist in the foreign tables even after the load is complete? (Clearly this impacts the referential integrity of the end set, the very purpose of foreign keys)
If the above sorts points 3/4, then the process could be accomplished, but not "nicely", using a script task to connect to the source table, obtain the data, and write it into the destination server... but I would question whether this is a case where SSIS may not be the best tool, and I'd think more about what you're trying to achieve overall. (If all you have is a hammer, everything tends to look like a nail). You would also want to build some error handling in here to deal with the potential issues of schema mismatch.
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.
19 People are following this question.
