Pivot using SSIS 2008 or SSIS 2008 R2...

Creating Pivot Table using SSIS 2008 or SSIS 2008 R2

Pivot is a Mechanism used to transform the Rows into Columns. Pivot transformation in Integration Services does the same. Create a table called "YearlyHours" in a Database and insert the records given below

Create Table YearlyHours

(

            YearlyID int identity(1,1),

            Months Varchar(100),

            WeekName Varchar(100),

            Expenses int

)

Insert YearlyHours Values('January', 'Sunday', 20000)

Insert YearlyHours Values('January', 'Monday', 30000)

Insert YearlyHours Values('January', 'TuesDay', 40000)

Insert YearlyHours Values('January', 'Wednesday', 50000)

Insert YearlyHours Values('January', 'Thursday', 60000)

Insert YearlyHours Values('January', 'Friday', 70000)

Insert YearlyHours Values('January', 'Saturday', 80000)

Insert YearlyHours Values('February', 'Sunday', 20000)

Insert YearlyHours Values('February', 'Monday', 30000)

Insert YearlyHours Values('February', 'TuesDay', 40000)

Insert YearlyHours Values('February', 'Wednesday', 50000)

Insert YearlyHours Values('February', 'Thursday', 60000)

Insert YearlyHours Values('February', 'Friday', 70000)

Insert YearlyHours Values('February', 'Saturday', 80000)

Insert YearlyHours Values('March', 'Sunday', 20000)

Insert YearlyHours Values('March', 'Monday', 30000)

Insert YearlyHours Values('March', 'TuesDay', 40000)

Insert YearlyHours Values('March', 'Wednesday', 50000)

Insert YearlyHours Values('March', 'Thursday', 60000)

Insert YearlyHours Values('March', 'Friday', 70000)

Insert YearlyHours Values('March', 'Saturday', 80000)

Insert YearlyHours Values('April', 'Sunday', 20000)

Insert YearlyHours Values('April', 'Sunday', 20000)

Insert YearlyHours Values('April', 'Monday', 30000)

Insert YearlyHours Values('April', 'TuesDay', 40000)

Insert YearlyHours Values('April', 'Wednesday', 50000)

Insert YearlyHours Values('April', 'Thursday', 60000)

Insert YearlyHours Values('April', 'Friday', 70000)

Insert YearlyHours Values('April', 'Saturday', 80000)

Insert YearlyHours Values('April', 'Sunday', 20000)

Insert YearlyHours Values('April', 'Monday', 30000)

Insert YearlyHours Values('April', 'TuesDay', 40000)

Insert YearlyHours Values('April', 'Wednesday', 50000)

Insert YearlyHours Values('April', 'Thursday', 60000)

Insert YearlyHours Values('April', 'Friday', 70000)

Insert YearlyHours Values('April', 'Saturday', 80000)

Insert YearlyHours Values('May', 'Sunday', 20000)

Insert YearlyHours Values('May', 'Monday', 30000)

Insert YearlyHours Values('May', 'TuesDay', 40000)

Insert YearlyHours Values('May', 'Wednesday', 50000)

Insert YearlyHours Values('May', 'Thursday', 60000)

Insert YearlyHours Values('May', 'Friday', 70000)

Insert YearlyHours Values('May', 'Saturday', 80000)

Insert YearlyHours Values('June', 'Sunday', 20000)

Insert YearlyHours Values('June', 'Monday', 30000)

Insert YearlyHours Values('June', 'TuesDay', 40000)

Insert YearlyHours Values('June', 'Wednesday', 50000)

Insert YearlyHours Values('June', 'Thursday', 60000)

Insert YearlyHours Values('June', 'Friday', 70000)

Insert YearlyHours Values('June', 'Saturday', 80000)

Insert YearlyHours Values('July', 'Sunday', 20000)

Insert YearlyHours Values('July', 'Monday', 30000)

Insert YearlyHours Values('July', 'TuesDay', 40000)

Insert YearlyHours Values('July', 'Wednesday', 50000)

Insert YearlyHours Values('July', 'Thursday', 60000)

Insert YearlyHours Values('July', 'Friday', 70000)

Insert YearlyHours Values('July', 'Saturday', 80000)

Insert YearlyHours Values('August', 'Sunday', 20000)

Insert YearlyHours Values('August', 'Monday', 30000)

Insert YearlyHours Values('August', 'TuesDay', 40000)

Insert YearlyHours Values('August', 'Wednesday', 50000)

Insert YearlyHours Values('August', 'Thursday', 60000)

Insert YearlyHours Values('August', 'Friday', 70000)

Insert YearlyHours Values('August', 'Saturday', 80000)

Insert YearlyHours Values('September', 'Sunday', 20000)

Insert YearlyHours Values('September', 'Monday', 30000)

Insert YearlyHours Values('September', 'TuesDay', 40000)

Insert YearlyHours Values('September', 'Wednesday', 50000)

Insert YearlyHours Values('September', 'Thursday', 60000)

Insert YearlyHours Values('September', 'Friday', 70000)

Insert YearlyHours Values('September', 'Saturday', 80000)

Insert YearlyHours Values('October', 'Sunday', 20000)

Insert YearlyHours Values('October', 'Monday', 30000)

Insert YearlyHours Values('October', 'TuesDay', 40000)

Insert YearlyHours Values('October', 'Wednesday', 50000)

Insert YearlyHours Values('October', 'Thursday', 60000)

Insert YearlyHours Values('October', 'Friday', 70000)

Insert YearlyHours Values('October', 'Saturday', 80000)

Insert YearlyHours Values('November', 'Sunday', 20000)

Insert YearlyHours Values('November', 'Monday', 30000)

Insert YearlyHours Values('November', 'TuesDay', 40000)

Insert YearlyHours Values('November', 'Wednesday', 50000)

Insert YearlyHours Values('November', 'Thursday', 60000)

Insert YearlyHours Values('November', 'Friday', 70000)

Insert YearlyHours Values('November', 'Saturday', 80000)

Insert YearlyHours Values('December', 'Sunday', 20000)

Insert YearlyHours Values('December', 'Monday', 30000)

Insert YearlyHours Values('December', 'TuesDay', 40000)

Insert YearlyHours Values('December', 'Wednesday', 50000)

Insert YearlyHours Values('December', 'Thursday', 60000)

Insert YearlyHours Values('December', 'Friday', 70000)

Insert YearlyHours Values('December', 'Saturday', 80000)

Select * from YearlyHours

Open the BIDS, drag and drop the Dataflow task. once done add the Oledb Source as given below

Add the OLEDB Source with the table already created with the name "YearlyName"

Add the Pivot transformation to the Data flow

Select the available Input columns as shown above in the Input Columns

Find the Input Columns present in Input Output Properties. Output columns has to be created as "Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday" using Add Columns.

Create a Output columns named Months and get the Lineage ID present in Input column of Month. Add the Lineage ID value into the Output column of Month in Source Column.

Create a Output columns named Months and get the Lineage ID present in Input column of Month. Add the Lineage ID value into the Output column of Month in Source Column. Run the DTSX Package now.

What was the cause of Error? it say duplication of data. 

What can be done to solve this Error? 

Add a Sort transformation, next to OLEDB Source as shown below

Edit the Sort transformation as shown

Run the Package now and check the Data Viewers

So Sort transformation plays important role in Pivot

Note : Kindly post the data with Full Will and Full Heart. I found some of the blog which Authors has posted data and screenshot to the Users without use. Kindly explain the full logic and screenshots which will be always remembered by Users. I.e. Examples like Sort which i explained earlier.  I have seen many blog which doesn't have such Sort Transformation. So i came forward to explain these Transformation.

Regards,

Daniel J,

Dataware and Data Mining Architect...