Merge records in a sorted order in SSIS.

• Learner093

  Old Hand

  Points: 359

  More actions

  May 3, 2013 at 9:37 am

  #277346

  I have a set of record types saying TH as Header and T1a, T1b, T1c, T1d, T1e, T2a, T2b, T2c, T2d, T2e as other type of records. TH table has a SortKey column with values 1, 2, 3, 4, 5.

  All these record types are tied by a column called RecordID. The output fashion I desire is below having corresponding number of records listed under associated TH record:

  RecordType RecordID SortKey

  TH 3678 1

  T1a 3678

  T1b 3678

  T1e 3678

  TH 2455 2

  T1c 2445

  T2d 2445

  TH 4522 3

  T2d 4522

  The SortKey order has to be maintained in 1, 2, 3 fashion in output file, and keeping all the record types tied by RecordID because the output file has to be read in the order of SortKey.

  I am doing this all in SSIS using Merge task to have the records merged as TH ->T1a ->T1b....

  Thank you for you help!

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  May 6, 2013 at 7:29 am

  #1612626

  Learner093 (5/3/2013)

  ---

  I have a set of record types saying TH as Header and T1a, T1b, T1c, T1d, T1e, T2a, T2b, T2c, T2d, T2e as other type of records. TH table has a SortKey column with values 1, 2, 3, 4, 5.

  All these record types are tied by a column called RecordID. The output fashion I desire is below having corresponding number of records listed under associated TH record:

  RecordType RecordID SortKey

  TH 3678 1

  T1a 3678

  T1b 3678

  T1e 3678

  TH 2455 2

  T1c 2445

  T2d 2445

  TH 4522 3

  T2d 4522

  The SortKey order has to be maintained in 1, 2, 3 fashion in output file, and keeping all the record types tied by RecordID because the output file has to be read in the order of SortKey.

  I am doing this all in SSIS using Merge task to have the records merged as TH ->T1a ->T1b....

  Thank you for you help!

  You haven't asked a question, so how can we help?

  ---