none
In SSIS I want to write Parent child (1 to Many) relationship data from two Raw files into a file

    Question

  • I have two rawfiles in SSIS one table is Parent records another rawfile has child records (1 to Many relationship). I want to write these data into a file with one parent record and all it's child records then next parent record and it's all child record. It will be greate help if some one has the SSIS soultion for this.

    Thanks in Advance

    Om

    Saturday, January 29, 2011 5:13 PM

Answers

  • The easiest way to do this is using Merge transformation of SSIS. The scenario you should follow is like this:

    1- Add 2 Raw File Sources to your Data flow tab, one for each file;

    2- Add 2 Sort transformations to your Data Flow tab, connect the output of your sources to the Sort transformation, and configure the Sort transformation based on the columns you want to sort the data in Raw File Source. This step is necessary, because Merge transformation accepts only sorted data as its input;

    3- Add a Merge transformation to Data Flow tab, and connect the output of the Sort transformations to it. Note that to get the parent rows on top of associated child rows, you should connect the output from Sort transformation that gets input from your master file;

    4- Connect the output of the Merge transformation to a OLE Db or ADO.NET destination.

    and you're done! Run and test your package.


    Please mark as answer if this helps. Thank you!

    http://thebipalace.wordpress.com

    Monday, January 31, 2011 1:43 AM
  • you can use script component as destination to implement this,

    first read data from two flat file source, then sort them, them merge them with merge join transform

    then connect output of merge join to a script component as destination , and in this script component write .net code to fill destination flat file with System.IO.StreamWriter class.

     

     


    http://www.rad.pasfu.com
    Saturday, January 29, 2011 5:22 PM
    Moderator

All replies

  • you can use script component as destination to implement this,

    first read data from two flat file source, then sort them, them merge them with merge join transform

    then connect output of merge join to a script component as destination , and in this script component write .net code to fill destination flat file with System.IO.StreamWriter class.

     

     


    http://www.rad.pasfu.com
    Saturday, January 29, 2011 5:22 PM
    Moderator
  • The easiest way to do this is using Merge transformation of SSIS. The scenario you should follow is like this:

    1- Add 2 Raw File Sources to your Data flow tab, one for each file;

    2- Add 2 Sort transformations to your Data Flow tab, connect the output of your sources to the Sort transformation, and configure the Sort transformation based on the columns you want to sort the data in Raw File Source. This step is necessary, because Merge transformation accepts only sorted data as its input;

    3- Add a Merge transformation to Data Flow tab, and connect the output of the Sort transformations to it. Note that to get the parent rows on top of associated child rows, you should connect the output from Sort transformation that gets input from your master file;

    4- Connect the output of the Merge transformation to a OLE Db or ADO.NET destination.

    and you're done! Run and test your package.


    Please mark as answer if this helps. Thank you!

    http://thebipalace.wordpress.com

    Monday, January 31, 2011 1:43 AM