Syncing AS400 and MSSQL tables RRS feed

  • Question

  • I am researching methods of syncing some of our AS400 tables with MSSQL, as our company is slowly moving towards .net from a long AS400 background. We have host integration services 2006(which we use for talking to AS400 objects in C#), SQL Server 2005 and the iSeries. Is this possible?

    We have tried using Integration Services to create a DTS package although the best we could come up with was nightly dropping the SQL table and copying the entire contents over to SQL again. Clearly this is only acceptable for tables that aren't updating frequently, and any changes to the SQL will be overwritten each night. I can't seem to find any information about this online even though I would think this should be a fairly common task.

    Wednesday, July 22, 2009 4:43 PM


  • I wouldn't use BizTalk for it.

    I would use SSIS. I am curious to know why you needed to drop the table every night... was there no "updated" column on the source data? BizTalk won't give you anything SSIS can't give you for database synchronization.  If you can't tell what rows are updated, all rows need to be copied and all data in the destination system needs to be overwritten. BizTalk can do it, only a lot slower than SSIS :-)

    eliasen, representing himself and not the company he works for.
    Three times MVP and three times MCTS in BizTalk.
    Blog: http://blog.eliasen.dk
    Wednesday, July 22, 2009 5:27 PM