CODEWITHSUNDEEP
ssismsbi
Arjun
Arjun

Reputation: 1069

SSIS Advanced Denormalization

I have to de-normalize data using SSIS, I tried using unpivot. but it gave me output like this. I have a different req. please suggest data flow steps. thank you.

product calss   type    Value
prod1   classA  AFD     2
prod1   classA  AFD_TS  6/29/2017
prod1   classA  CCD     5
prod1   classA  CCD_TS  7/4/2017
prod1   classA  MSD     8
prod1   classA  MSD_TS  7/9/2017

original input

product calss   AFD AFD_TS      CCD CCD_TS      MSD MSD_TS
prod1   classA  2   6/29/2017   5   7/4/2017    8   7/9/2017
prod1   classB  3   6/30/2017   6   7/5/2017    9   7/10/2017
prod1   classC  4   7/1/2017    7   7/6/2017    1   7/11/2017
prod2   classA  12  7/2/2017    15  7/7/2017    18  7/12/2017
prod2   classB  13  7/3/2017    16  7/8/2017    19  7/13/2017
prod2   classC  14  7/4/2017    17  7/9/2017    11  7/14/2017

expected output

product calss   type Value TS
prod1   classA  AFD 2   6/29/2017
prod1   classA  CCD 5   7/4/2017
prod1   classA  MSD 8   7/9/2017
prod1   classB  AFD 3   6/30/2017
prod1   classB  CCD 6   7/5/2017
prod1   classB  MSD 9   7/10/2017
prod1   classC  AFD 4   7/1/2017
prod1   classC  CCD 7   7/6/2017
prod1   classC  MSD 1   7/11/2017
prod2   classA  AFD 12  7/2/2017
prod2   classA  CCD 15  7/7/2017
prod2   classA  MSD 18  7/12/2017
prod2   classB  AFD 13  7/3/2017
prod2   classB  CCD 16  7/8/2017
prod2   classB  MSD 19  7/13/2017
prod2   classC  AFD 14  7/4/2017
prod2   classC  CCD 17  7/9/2017
prod2   classC  MSD 11  7/14/2017

Upvotes: 0

Views: 78

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

For this I would use an asynchronous script transformation.

For each row that you pass in, create three output rows, one for AFD, one for CCD, and one for MSD.

You can make your output asynchronous by setting its SynchronousInputID property to 'None' on the 'Inputs and Outputs' tab.

Upvotes: 2

Related Questions