Search code examples
importssisflat

Combing two input flat files in SSIS

I have two flat file inputs of the following format:

File 1-

AccountNumber1    Data1
AccountNumber1    Data2
AccountNumber1    Data3
AccountNumber2    Data1
AccountNumber3    Data1

File 2-

AccountNumber1    OtherData1
AccountNumber1    OtherData2
AccountNumber2    OtherData1
AccountNumber2    OtherData2
AccountNumber3    OtherData1

I need to apply a transformation that achieves the following:

AccountNumber1    Data1
AccountNumber1    Data2
AccountNumber1    Data3
AccountNumber1    OtherData1
AccountNumber1    OtherData2
AccountNumber2    Data1
AccountNumber2    OtherData1
AccountNumber2    OtherData2
AccountNumber3    Data1
AccountNumber3    OtherData1

That is to say, I need all of the account rows to stay together. The Union task doesn't seem to be able to accomplish what I need because I have to maintain the arbitrary sort order of the first file and simply insert file 2's rows where the account numbers match. Is there a way to accomplish this without a script task?

Solution

  • Use Derived Column for both sources to add a column of 1s to the first and a column of 2s to the second table:

    Table1:

     AccountNumber1  ,  Data1 , 1

 AccountNumber1  ,  Data2 , 1

 AccountNumber1  ,  Data3 , 1

 AccountNumber2  ,  Data1 , 1

 AccountNumber3  ,  Data1 , 1

    Table2: 

     AccountNumber1  ,  OtherData1 , 2

 AccountNumber1  ,  OtherData2 , 2

 AccountNumber2  ,  OtherData1 , 2

 AccountNumber2  ,  OtherData2 , 2

 AccountNumber3  ,  OtherData1 , 2

    Now, first Use Union All, and then use Sort by AccountNumber and DerivedColumn in order.