SSIS - How To Use Unpivot In SSIS

Scenario: 

We have a source table that has columns SaleQ1,SaleQ2,SaleQ3,SaleQ3 for sale amount. We want to Unpivot and produce output in given format.

Source table definition with sample data
CREATE TABLE dbo.TblSource
  (
     SalePerson VARCHAR(100),
     SaleYear   INT,
     SaleQ1     INT,
     SaleQ2     INT,
     SaleQ3     INT,
     SaleQ4     INT
  )
Insert into dbo.TblSource
values ('Aamir',2011,100,120,130,140),
('Aamir',2013,12,13,14,15),
('Raza',2012,101,122,133,150),
('Singh',2012,Null,Null,Null,1000)

Solution: 

We will be using Unpivot Transformation in SSIS to get our expected output.

Step 1: 

Bring Data Flow task to Control Flow pane and then drag OLE DB Source to Data Flow Pane and make connection to your source database and provide the source table as shown below.

Step 2:

Bring Unpivot transformation to Data Flow pane and then connect OLE DB Source to it. Double click on Unpivot transformation to configure it.

Choose the columns those you want to pass through without any change. Also choose the columns as input on which you want to perform the unpivot action.

Provide the Destination column that is going to have the values for Input Columns.Also provide the Pivot Key value for Pivot Key value column.

Final Output : 

Bring the multicast transformation and connect Unpivot transformation to it. Add DataViewer so we can display the output generated by Unpivot transformation. 

If you notice that Singh has only one record for Quarter 4 as other quarters had Null values for Singh and Unpivot ignored those instead of generating rows with Null Sale.