reading excel which contains the mapping columns of 2 different DBs & migrate it using SSIS


  • hi, i m new into SSIS, we have a requirement, in which I need to migrated data from 20 different DB into single universal DB.
    20 different DBs have different structure. So we have a excel containing the mapping columns for all these 20 DBs tables.
    So can anyone help me out how to read excel, fetch the mapping columns and do the migration using SSIS.

    Thanks in advance

    Friday, February 21, 2014 10:02 AM

All replies

  • Creating such a package is best using the programmatic approach.

    So 1st your create .net code (outside SSIS scope) that reads the Excel and creates a package per each source-target pair, then you run it/them.

    You will find a ton of information if you just Bing/Google, but the reality is, these rarely work smooth.

    So it may be a wasted effort especially after the fact it is a one time procedure.

    Arthur My Blog

    Monday, February 24, 2014 4:54 PM
  • I would have created this using 20 separate DFT tasks. Just define connection managers for sources and destination and use 20 DFTs with OLEDB siurce to connect to source dbss and OLEDB destination to connect to destination. use derived column in between if you want to do any simple transformations.

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Monday, February 24, 2014 6:11 PM
  • hi, actually these excel sheet may be changed at any point of time  i.e., it will be dynamic, it gets changed from time to time if any new column is added r merged from source DB.


    Tuesday, February 25, 2014 7:01 AM
  • then let me repeat myself: Creating such a package is best using the programmatic approach.

    I can tell you that what you try to deliver is going to be fragile. Don't expect the process to work reliably due to its input variability/volatility. 

    Arthur My Blog

    Tuesday, February 25, 2014 3:05 PM