none
How load data row by row to destination tables using SSIS for each loop container RRS feed

  • Question

  • Hi Team,

    1. Have 20 tables in my database. 

    2. In one table having two reference keys (empId,SubCatID) with associated with remain 19 tables.

    3. Each table have primary key

    4. Want to load data row by row in each table 

    for example:

    Have one record as empId=1. Want to load this record into table2,table3...table20.

    after that empId=2 process to load table 2 to 20.

    If it is possible using SSIS foreach loop container, please help on same


    Thanks Bala Narasimha

    Tuesday, October 15, 2019 10:24 AM

All replies

  • Hi Bala,

    Foremost, I do see a need in doing so - you can load the 19 [parent] tables and then the last one that references with (empId,SubCatID), or you can just disable/drop the Foreign keys load all the tables and re-constitute the relationships.


    Arthur

    MyBlog


    Twitter

    Tuesday, October 15, 2019 1:10 PM
    Moderator
  • Hi Bala,

    Please use Execute SQL Task, Foreach Loop Container and Precedence Constraint

    as the following picture shown.

    Best Regards,

    Mona


    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

    Wednesday, October 16, 2019 9:22 AM
  • Thanks for response.

    Could you please tell me which enumerator your used in foreach loop container and if it is possible please share above package step by step screenshots or please share package file.


    Thanks Bala Narasimha

    Thursday, October 17, 2019 4:12 AM
  • Hi Bala,

    Please see the following pictures:

    Best Regards,

    Mona


    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

    Thursday, October 17, 2019 6:23 AM
  • Thanks for response

    any setting required for sequence container?


    Thanks Bala Narasimha

    Thursday, October 17, 2019 11:54 AM
  • Hi Bala,

    No, there is nothing in sequence container.

    Best Regards,

    Mona


    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, October 18, 2019 2:40 AM
  • Thanks for response.

    as per your guidance have created one package as shown in bellow.

     

    When am try execute package data is not loading to destinations.

     

    Could you please help on this. did i miss something over here.


    Thanks Bala Narasimha

    Friday, October 18, 2019 5:20 AM
  • You have a constraint (fx) in the output path of the sequence Container, what is that for an expression; seems the condition isn't meet?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 18, 2019 6:32 AM
  • Thanks for reply 

    This is my condition


    Thanks Bala Narasimha

    Friday, October 18, 2019 7:00 AM
  • Hi Bala,

    Please try the updating solution as the following pictures shown:

    Script in Script Task:

    public void Main()
    {
        MessageBox.Show(Dts.Variables["empId"].Value.ToString());
        Dts.TaskResult = (int)ScriptResults.Success;
    }

    Best Regards,

    Mona


    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, October 18, 2019 8:21 AM