Clever Geek Handbook

Compressing two input flat files in SSIS

I have two flat input files in 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 transform that provides the following:

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

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

+3


source to share


1 answer


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

Table1:

 AccountNumber1  ,  Data1 , 1

 AccountNumber1  ,  Data2 , 1

 AccountNumber1  ,  Data3 , 1

 AccountNumber2  ,  Data1 , 1

 AccountNumber3  ,  Data1 , 1


Table 2:

 AccountNumber1  ,  OtherData1 , 2

 AccountNumber1  ,  OtherData2 , 2

 AccountNumber2  ,  OtherData1 , 2

 AccountNumber2  ,  OtherData2 , 2

 AccountNumber3  ,  OtherData1 , 2

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

+2


source






More articles:


All Articles