PIVOT task in Data Flow Transformation in SSIS 2008

Well, I have written so many articles for PIVOT in my blog but today I am going to show you PIVOT task in Microsoft SQL Server Integration Services 2008 (SSIS).

BTW, before we start PIVOT in SSIS, if anybody would like to have a look at PIVOT examples with TSQL, than kindly follow below link:

http://www.sqlhub.com/2009/05/dynamic-pivot-with-month-number-to.html

http://www.sqlhub.com/2009/03/dynamic-pivot-with-where-condition-in.html

http://www.sqlhub.com/2009/03/dynamic-cross-tab-query-without-cursor.html

http://www.sqlhub.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

http://www.sqlhub.com/2009/03/pivot-fixed-column-cross-tab-query-in.html

Now back to the point. Let us move ahead with our PIVOT Task in SSIS. Before we go to BIDS and start working on SSIS, we have to have some data which we can PIVOT so let us create one table, insert some data in SQL Server database first.

CREATE TABLE StudDetail

(

StudentID INT,

PassYear VARCHAR(10),

Grades int,

Increase INT,

Decrease INT

)

GO

INSERT INTO StudDetail

SELECT 1, '08-09', 3333,0,0 union all

SELECT 1, '09-10', 4252,25,0 union all

SELECT 2, '08-09', 2100,0,0 union all

SELECT 2, '09-10', 2002,0,-10

GO

Select * from StudDetail

So, now you are ready to go.  Please create new project in BIDS and put one Data Flow Task in “Control Flow” tab. Double click on it to configure. As soon as you double click on “Data Flow Task” from “Control Flow” tab. You will be landed to “Data Flow” tab.

Now, drag and drop OLEDB Source task and configure it with the database in which you have created table “StudDetail”.

Select “SQL Command” in “Data Access” mode and use below given TSQL query.

SELECT StudentID ,PassYear,Grades  FROM StudDetail

Note: If you don’t know how to configure “OLEDB Source”, kindly click here.

Now, drag and drop “Pivot” task below “OLEDB Source” and Drag the green (on the left) data flow output to the Pivot component and drop it to create precedence.

Now we are going to configure “Pivot” task and this is the trickiest part in this article. Once you double click on PIVOT, you will get “Advanced Editor for Pivot” dialog box with three tabs.

1.)    “Component Properties”

2.)    “Input Columns”

3.)    “Input and Output Properties”

You don’t need to do anything in first tab.

Check weather all three columns are selected in second tab (Input Columns).

Now, go to 3^rd tab, named “Input and Output Properties” and expand

Pivot Default Input->Input Columns

You will see all three columns there 1.) StudentID 2.) PassYear 3.) Grades. we have to configure “Pivot Usage” property for that. Set 1 for first column 2 for second column and 3 for third column.

Also remember the “LineAgeID” property for each column which will be used later and plays an important role.

Now, expand Pivot Default Output->Output Column. You won’t find any column there so far, just add three columns there by clicking “Add Column” Button.

1.)    StudentID

2.)    [08-09]

3.)    [09-10]

Output of Column StudentID would come from StudentID column in Input section. Do you remember LineAgeID property of StudentID column in above screen shot? It is 17 there so “Source Column” property of “StudentID” in output should be 17, for more details, see below screen shot.

Now, let us set property for [08-09] and [09-10] columns in output.  LineAgeID property for column Grades in Input section is 23 right now so [08-09] will have 23 in SourceColumn property and if value of PassYear would be 08-09 than it should come in our output column [08-09] so PivotKeyValue property should be 08-09.  Look at the screen shot for more details:

Same way, properties should be set for column [09-10] too.

Now, you are ready to have your output, you can have your output in screen or in any file like flat file, spreadsheet etc.  Right now I just wanted to see the results on screen.

Create one Variable of Object type, named “Results”. If you don’t know how to create variable, please read this article.

Now, I am taking one more task “Recordset Destination”, double click on that to configure it.  In a “VariableName” property, select the “Results” variable of Object type which you have just created.  Go to “Input column” tab to see whether all three columns are selected and click on “OK” button.

Since I wanted to see results in screen (Grid View), double click on precedence (Green Arrow) between Pivot task and Recordset Destination,  go to “Data Viewers” tab, click on “Add” button, now select “Grid” and press OK button two times and run your package by hitting F5.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com