September 20, 2005 at 1:19 am
I am sorry for having posted this with an improper subject. I hit the submit button a bit soon.
I have a requirement where in I need to synchronize my legacy Progress database to SQL Server.
a) whenever a row gets inserted into my Progress tables, I need this row to get inserted into my SQL Server table as well.
b) The Progress tables are not normalized. Hence, in some cases, a column in Progress table maps to 3 columns in SQL Server tables. I need to do such a mapping in SSIS. Also, I need to slice the column data from Progress and send the individual slices to each of the 3 columns in SQL Server.
c) When a table gets updated in Progress, I want 3 tables in SQL Server to get updated. And, all the 3 updates should happen or none should happen.
d) I need to have transaction ordering support. For e.g. in Progress, table T1 and table T2 get updated. So, in SQL Server, I want the table T2 to get updated first and then T1 should get updated.
e) There is a reverse synchronization requirement from SQL Server to Progress. Whenever a row in SQL Server gets updated, I need to update the corresponding tables in Progress as well.
Can I achieve all these things using SSIS? I am evaluating SSIS to solve the above problems. Any help in this regard would be appreciated.
September 21, 2005 at 2:54 pm
Yes and no. That is, SSIS can be part of a solution, but if you want two-way synchronization with two live databases, you will need some sort of mechanism to tell you which tables need to be updated. Something like triggers and metadata tables. If you have something like identity columns in your Progress db, you'd need a way to manage those so you don't get conflicts.
Dylan Peters
SQL Server DBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply