none
SSIS Data Flow - Automatic updating table whenever a different table is updated.

    Question

  • I need to update Table Parent, whenever there is a new set of records added to Child. There are about 6 Child tables and whenever there is a update in any of the table, I need to kick out the existing rows for that child in parent table and add new records in child to parent. The whole process should be automated, i.e., whenever there is a notification of new rows in child, it should trigger the above mentioned operation.

    I am very new to SSIS, so any kind of ideas to start and fulfill this task will be very helpful.

    Thursday, December 6, 2018 7:38 PM

All replies

  • You can create a SSIS package with data flow task to extract data from child and then use lookup task to compare rows with parent on primary key columns. The match output will give you existing rows in parent which you can delete using OLEDB Command. Then insert the changed/ new rows from child to staging table

    Add execute sql task to populate target table with rows from staging table

    So full package will look like these

    1. Data flow task

     inside it

        a. OLED Source - get rows from child

        b. Lookup task - compare with parent on key columns

         c. No match output - link to OLEDB destination to insert to Staging

         d. Match output - link to Multi cast to take two copies of outputs

         e. One copy link to OLEDB command to delete rows from parent

         f. Other copy link to OLEDB destination to insert to Staging

    2. Execute SQL task - Insert all rows from Staging to Final target table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Alkehya Friday, December 7, 2018 1:47 PM
    • Unmarked as answer by Alkehya Friday, December 7, 2018 7:52 PM
    Thursday, December 6, 2018 8:01 PM
  • Hi Alkehya, 

    @Visakh has already given an excellent solution for package design. 

    To run the package automatically when new records inserted into child tables, please check the discussion in these threads: 

    SSIS Trigger when Database table changed

    How to execute Sql Server Agent job from Trigger


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 7, 2018 6:54 AM
  • Thank you for your reply
    Friday, December 7, 2018 1:47 PM
  • Thank you for the reply.
    Friday, December 7, 2018 1:48 PM
  • Hi Alkehya, 

    Why did you unmark this reply and reopen this thread?

    Any error message or not the solution you need? 

    If you have any question, please let we know. 


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 11, 2018 1:30 AM