Porting data from DB2 to SQL server

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Porting data from DB2 to SQL server

Viewing 1 reply thread
  • Author
    Posts
    • #18299

      Hi all,

      i have a requirment to port data from DB2 to SQL.Is it possible to port data without webservice?

      please help on this issue.

      Give me some urls or examples

      Guhananth.S 

       

    • #18302

      Hi Guhanath,

      Before I dive into a BizTalk answer, I'd probably look at doing this using SSIS packages directly from SQL Server (SSIS are the new DTS) – this might be an easier option and would produce faster results.

      If however you're intent on doing this through BizTalk, you'll need to get hold of the DB2 Adapter, available free of charge, which can be found here (it ships as part of the Microsoft BizTalk Adapters for Host Systems).

      In terms of the solution, you have two options:

      • If you plan on polling the DB2 for changes (and can determine what has changed when you poll DB2), you can use a messaging-only solution: setup a receive port/location using the DB2 adapter to poll once every x seconds/minutes/hours – records will be returned into the MsgBox. Setup a Send Port to subscribe to messages coming from the receive port and map from the DB2 message to a SQL Server Updategram message, squirting the message at your SQL Server instance using the out-of-the-box SQL Adapter. For this solution, you will need a schema representing the DB2 message and SQL Server message and a map between the two.
      • If you need to receive a trigger message to kick-off the poll of DB2, you will need to run this in an orchestration: the orchestration will receive the trigger message and initiate a solicit-response call to DB2 (using the DB2 adapter), returning an Xml message representing the records to be inserted into SQL Server. A map can be applied in the orchestration converting the message from a DB2 record into a SQL Server updategram record and a Send Port will then be used to squirt the message at your SQL Server instance using the out-of-the-box SQL Adapter. For this solution, you will need a schema representing the DB2 message (request and response) and SQL Server message (request; response is not necessarily required) and a map between the DB2 response and SQL Server Request; the trigger message can be defined as an XmlDocument as you'll throw it away once received.

      Note, that I've just read that the DB2 adapter will return a single message for each record returned – you'll need to check this as the orchestration becomes a little more complicated than that described above. Happy to elaborate if you need anymore advice.

      Please bear in mind that this is a brief overview of the two possible solutions, however, the following links should help you with your solution:

       Hope this helps,

      Nick.
       

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.