Pivot and UnPivot with SSIS

Introduction

The presentation of your data is required for easy analysis. Turning columns into rows and rows into columns is another way of presenting your data so that end-users can understand it easily. Other than presentation purposes, you need to transform your data within your data warehouse application into different data formats. This process can be referred to as ‘Pivoting’, and the reversing of this process can be referred to as ‘UnPivoting’.

In this article, we will discuss how you can use the Pivot and Unpivot processes in SQL Server Integration Service (SSIS).

Requirements

As this article will not tell you how to write SSIS packages, it is a requirement to have a fair amount of experience in creating SSIS packages. However, I will try to discuss details of creating SSIS packages wherever necessary without disturbing the main topics of the article. In addition, it is necessary to know how to write a query with joining three or more tables.

Software requirements include SQL Server 2005 installed with SQL Server Business Intelligence Development Studio. This article is written with the assumption that the Adventureworks database’s data is being used. It would be optimal for the reader to have an installed Adventureworks database along with a SQL Server 2005 database server.

The data file for this exercise can be found here .

Pivot

To understand what pivoting is, let’s see an example. Below illustrates the relationship in the Advenureworks database of Sales.SalesOrderHeader, Sales.SalesOrderOrder, Prodcution.Product and Production.ProductCategory tables.

From the above relationship, we can assume that we need following output.

It is known that you will not retrieve the above data set by a simple T-SQL query. However, we can build the following format with a simple T-SQL code.

Turning the above table into the required format is an example of “pivoting”.

Let’s start with the design package. First, add a new package after creating Integration Project.

Then add a Data flow task to control flow. Next, add an OLE DB connection and configure that OLE DB connection to the Adventureworks database.

Finally, add an OLE DB source to the added data flow. While we are going to design the SSIS package with minimum coding, we will use as many SSIS controls as possible. Thus, we will add following T-SQL to the added OLE DB Source.

SELECT PC.Name

,soh.OrderDate

,SOD.OrderQty

FROM Sales.SalesOrderDetail SOD

INNER

JOIN Sales.SalesOrderHeader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

INNER

JOIN Production.Product PROD

ON Prod.ProductID = SOD.ProductID

INNER JOIN production.ProductCategory PC

ON PROD. ProductSubcategoryID = PC.ProductCategoryID

After including the above code, the OLE DB Source will resemble the screenshot below.

We are going to add a condition split since we are doing this pivot for only a ‘year’. To improve the usability, I have included a variable name called ‘intYear’. Therefore, if you want to change the year, it is just a matter of changing the value of the variable ‘intYear’.

The following is a screenshot of the conditional split.

In the above case, the value of @User::intYear is set to 2001.