locked
Multiple sources and one destination in data flow task RRS feed

  • Question

  • Hi,

    I have 3 tables with different columns i have one destination to load the data from these 3 different tables into the final destination table. I want to do it in the SSIS package. Is it i need 3 data flow tasks to load the data or 1 data flow task is fine. Please suggest the best.

     

    Thanks


    latha
    Monday, August 1, 2011 12:54 PM

Answers

  • You can do it in one data flow task. You could use a merge transformatoin http://msdn.microsoft.com/en-us/library/ms141703(v=SQL.110)
    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 1:09 PM
  • I created a sample package and it works successfully. Here's the flow of the package.

                    OLEDB source1              OLEDB source2                OLEDB source3
                            |                               |
                            Sort                        Sort                                      |
                              |                           |                                         |
                          ---------- -----------------------                                |
                                             |                                                   Sort

                                          Merge Join1(Full Outer Join)                   |                                 
                                              |
                                             Sort                                                 |
                                              |

                                          MergeJoin2 (Full Outer Join)  -------------  | 
                                                |
                               OLEDB Destination

         

    The Data from the OLEDB Source3 will be sorted and then connects to Merge Join 2.  it will load data from 3 tables in single oledb destination. There is not much of change from the example in the link i provided http://www.ssistutorial.com/create_join_between_datasets_in_ssis.php

    Let me know if you have any questions.

    Thanks



    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 8:17 PM
  • Try Union All Transformation. But, the destination table will have lot of null values.

    thought process:

    I assume you have a destination table structure that can accommodate the values coming from 3 different tables. what I would suggest is have 3 parallel set of Data source and Destination. 

    Source 1 --> Destination A

    Source 2 --> Destination A

    Source 3 --> Destination A 

    if column names are different from from source to destination then you can manually map the source to destination column.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 3:44 PM
  • Hi ,

     Please  try this,

     

    Table A:

    AId Nam,e account , price   

    Table B:

    BId Units  product sales

    Table C:

    CId debts credits total

    --Get the Combination Of All the Table Rows

    SELECT

    [Table A]. *,

    [Table B]. *,

    [Table C]. *

    FROM

    [Table A], [Table B],[Table C]

    ---WHERE Col_Name ===Apply Your Filter Here /NULL Validation

     

     

     

     


    rajiv
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Tuesday, August 2, 2011 8:36 AM

All replies

  • You can do it in one data flow task. You could use a merge transformatoin http://msdn.microsoft.com/en-us/library/ms141703(v=SQL.110)
    Happy to help! Thanks. Regards and good Wishes, Deepak. In a revamp of my Blog!
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 1:09 PM
  • @sqlrockss:

    I dont have same no.of columns and also their data types are different in 3 tables i think i am not able to use Union All Transformation.Each table is different but the destination has the all the columns to take the data how can i do it using a simple data flow task.

    @deepak_msbi:

    I am not able to open the above link.


    latha
    Monday, August 1, 2011 1:46 PM
  • Hi,

    I have 3 tables with different columns i have one destination to load the data from these 3 different tables into the final destination table. I want to do it in the SSIS package. Is it i need 3 data flow tasks to load the data or 1 data flow task is fine. Please suggest the best.

     

    Thanks


    latha


    You need to provide much more information than what you have done here. People do want to help you but they can't do that if they need to guess what you're actually trying to achieve.

    Maybe start by providing the names and types of columns in the three sources and in the destination. Even better, explain how data from each column in the sources map to which column in the destination. Do you need to join the data from the three sources or is it enough to UNION them? if you need to join them, which columns do you need to join on?

    help us to help you.

     


    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson
    Monday, August 1, 2011 2:02 PM
  • Hi, you can achieve this using a Merge Join and sort transformation tasks but still its unclear how the data is coming from 3 tables to one destination table. Try this this link. It shows how to load data from 3 different tables to single OledB destination.

    http://www.ssistutorial.com/create_join_between_datasets_in_ssis.php


    Monday, August 1, 2011 2:34 PM
  • @Jamie thomson and @sqlRcokss:

    Assume i have three source tables as:

    Table A:

    AId Name account price

    Table B:

    BId Units  product sales

    Table C:

    CId debts credits total

    There is no relation between Table A ,B and C all the tables are independent of others and no.of rows and columns in each table is also different.Now the destination is like these:

    ID AID BID CID Name Units Product sales price account debts credits total

    If the value is not available i will place "NULL" in the column for respective row.  ID's are also "NULL" constraints.

     


    latha
    Monday, August 1, 2011 3:41 PM
  • Try Union All Transformation. But, the destination table will have lot of null values.

    thought process:

    I assume you have a destination table structure that can accommodate the values coming from 3 different tables. what I would suggest is have 3 parallel set of Data source and Destination. 

    Source 1 --> Destination A

    Source 2 --> Destination A

    Source 3 --> Destination A 

    if column names are different from from source to destination then you can manually map the source to destination column.


    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 3:44 PM
  • I created a sample package and it works successfully. Here's the flow of the package.

                    OLEDB source1              OLEDB source2                OLEDB source3
                            |                               |
                            Sort                        Sort                                      |
                              |                           |                                         |
                          ---------- -----------------------                                |
                                             |                                                   Sort

                                          Merge Join1(Full Outer Join)                   |                                 
                                              |
                                             Sort                                                 |
                                              |

                                          MergeJoin2 (Full Outer Join)  -------------  | 
                                                |
                               OLEDB Destination

         

    The Data from the OLEDB Source3 will be sorted and then connects to Merge Join 2.  it will load data from 3 tables in single oledb destination. There is not much of change from the example in the link i provided http://www.ssistutorial.com/create_join_between_datasets_in_ssis.php

    Let me know if you have any questions.

    Thanks



    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Monday, August 1, 2011 8:17 PM
  • Hi ,

     Please  try this,

     

    Table A:

    AId Nam,e account , price   

    Table B:

    BId Units  product sales

    Table C:

    CId debts credits total

    --Get the Combination Of All the Table Rows

    SELECT

    [Table A]. *,

    [Table B]. *,

    [Table C]. *

    FROM

    [Table A], [Table B],[Table C]

    ---WHERE Col_Name ===Apply Your Filter Here /NULL Validation

     

     

     

     


    rajiv
    • Marked as answer by latha521 Monday, August 8, 2011 5:12 PM
    Tuesday, August 2, 2011 8:36 AM