locked
Need some suggestion for the following Migrating data from one source table to many multiple destination tables RRS feed

  • Question

  • The task is to move data from legacy system to OLTP System.

    So let me discribe the scenario...legacy system has  loaded data into DB2 database

    Lets say legacy system has loaded Policy details into one table in DB2 Database.

    All the information (Complete) related to Policy has been stored into Policy table(Only one table which have all the info) in DB2 database.

    Now the client has moved the Policy info from DB2 database to Sql Server-SourceDB.

    Now the sql server has the policy information....

    All i have to do is...from the One Policy table (Denormalised - which has Policy,Claims,PolicyCoverage ..eveything in one table) i have to migrate all the records to Normalised Policy table in another sql server -DestinationDB.

    The(New) Normalised Policy table has the following structue

    MasterPolicy Table (PolicyId- Autogenerate as primary key -PM1)

    PolicyRelatedTable -1 (which has ID - as Autogenerate as and i has PolicyId which refers MasterPolicyTable -PolicyId foreign key)

    like this i have lots of ChildTables

    So when i insert one record into (DestinationDB)MasterPolicy table from(SourceDB)keeping PolicyId as the reference...

    which is the best way to do that..

    Using SSIS Package or writing stored procs...


    ilikemicrosoft

    Monday, May 28, 2012 6:10 AM

Answers

  • You can use SSIS packages to do this - first you must copy mastertables data then reference tables. 

    1. One full import

    2. daily incremental feeds


    http://uk.linkedin.com/in/ramjaddu

    Monday, May 28, 2012 10:25 AM
  • read chapter 12 page 404 from this book , you can download the sample for the llink,

    basically you have a header detail scenario, you can use SSIS to do the job for you , your challange will be to run the package with larg amount of data (I am working in the same field as you) and having enough memory .

    SSIS will run in parallel and will do the job.


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Saturday, June 2, 2012 5:18 AM

All replies

  • You can use SSIS packages to do this - first you must copy mastertables data then reference tables. 

    1. One full import

    2. daily incremental feeds


    http://uk.linkedin.com/in/ramjaddu

    Monday, May 28, 2012 10:25 AM
  • read chapter 12 page 404 from this book , you can download the sample for the llink,

    basically you have a header detail scenario, you can use SSIS to do the job for you , your challange will be to run the package with larg amount of data (I am working in the same field as you) and having enough memory .

    SSIS will run in parallel and will do the job.


    Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Saturday, June 2, 2012 5:18 AM