Rajendra Gupta
PIVOT example

An overview of SSIS Pivot and SSIS Unpivot Transformations

November 28, 2019 by

This article explores an SSIS Pivot transformation and SSIS Unpivot transformation for creating Pivot and Unpivot data tables.

Introduction

Pivot tables are an accessible and useful feature for data analytics purposes in Microsoft Excel. It is a reporting tool in the Excel sheets to calculate, analyze and summarize data. You also get data comparisons, trends of source data.

In the following image, we can see Pivot and Unpivot data conversions:

PIVOT example

  • Pivot – It converts individual row data into separate column data
  • Unpivot – It performs reverse data conversion of Pivot data. We get the actual data after Unpivot

SQL Server provides SQL PIVOT and SQL UNPIVOT functions in T-SQL. You can read more about these functions in Static and Dynamic SQL Pivot and Unpivot relational operator overview.

As explained in previous SSIS articles on SQLShack, SSIS is a friendly component that you can use in performing complex transformations with few steps. It does not require extensive programming knowledge. In this article, we will learn Pivot and Unpivot transformations using the SSIS package.

Pre-requisite for SSIS Pivot Transformation

Sample Database – AdventureWorks

For this article, I am using following AdventureWorks sample database.

Sample data

Visual Studio 2019

In this article, I am using SQL Server Data Tools in Visual Studio 2019. It is a new version of Visual Studio and undergone many changes. You can refer to Microsoft docs for changes in SSDT and downloading the installer.

Visual Studio 2019 configuration for Integration Service projects

Visual Studio 2019

Let’s have a quick summary of creating an SSIS package in Visual Studio 2019. Click on Continue without code:

Launch Visual Studio 2019

It launches the Microsoft Visual Studio. Click on Extensions followed by Manage Extensions:

Extensions in Visual Studio 2019

Search for Integration Services, and it shows available extensions:

Search for Integration Services

Click on Download in front of SQL Server Integration Services Projects. It starts downloading required packages in the default web browser:

Download Server Integration Services Projects

Run the executable installer and it starts the configuration of SSDT for Visual Studio. Select the language of the installer:

Language selection

Click OK and select the Visual Studio version. If you have multiple versions of Visual Studio installed, select the appropriate version:

Select Visual Studio version

Click on Install, and it completes the SQL Server Integration Service projects within a few minutes depending on machine specs. A system restart is required after a successful installation.

Configure an SSIS package for SSIS Pivot Transformation

Launch Visual Studio 2019 and click on File > New > Project on the menu bar or by clicking the New Project button on the toolbar. It opens project templates. Click on Integration Service Project:

Launch Integration Services project

Specify the project name and storage location. Click on checkbox Place solution and project in the same directory and Create:

Configure the project

It creates the project solution for SSIS Pivot Transformation and launches the default page of Visual Studio 2019:

Default page

Drag and drop Data Flow Task from SSIS toolbox, as shown below:

Drag and drop Data Flow Task

Right-click on Data Flow Task and rename it to PIVOT Transformations:

Rename the task

Double-click on it, and it opens the data flow tab.

Drag and drop the following components from SSIS toolbox in the data flow area:

  • OLE DB Source
  • Pivot Transformation

Add a Pivot transformation

Double-click on OLE DB Source and in the editor window, do following configurations:

OLE DB Source editor

  1. OLE DB connection managers: Specify a new connection or select from existing connection to SQL instance
  2. Data access mode: Select the data access mode as SQL command as we will use the select statement specified previously
  3. SQL command text: Specify the select statement for retrieving the records. We will use the output from this query in Pivot operation

Now, drag the arrow from the OLE DB Source to the SSIS Pivot Transformation:

pivot Transformation editor

Double-click on Pivot and it opens the edit the pivot transformation editor:

Pivot configuration options

Note: You might get a slightly different pivot transformation in a different version of SSIS. I always prefer to use the latest version

  1. Set key: In this section, we specify the column for left column values. Use [Name] column in the sort key from our sample data
  2. Pivot key: It specifies the column that we use for the header row in the Pivot table. We use [OrderYear] column in the Pivotkey
  3. Pivot Value : It specifies the column for table values apart from the header and left the column. Use [OrderQuantity] column in the Pivot value
  4. Ignore un-matched Pivot Key values and report them after DataFlow execution: We can use this option for ignoring un-matched pivot key values. It reports all such pivot key values in the logs

Specify the inputs in Pivot

As highlighted in the above screenshot, it provides a hint to execute the data flow in the debugger and copy the values in this window.

Click OK and add an SSIS multicast operator. We can add an OLE DB destination as well, but for this demonstration, we will add a multicast operator and view the Pivot data:

add an SSIS multicast operator.

Right-click on the connector between Pivot and Multicast operator. Click on Enable Data Viewer so that we can view the Pivot data:

Enable data viewer

Execute the SSIS Pivot Transformation package. In the progress bar, you can see Pivot values [2005],[2006],[2007],[2008] as shown below:

Pivot values

Right-click on this line and copy message:

Copy message text

Stop package debugging (Shift+F5):

Stop package debugging

Paste the message values in the highlighted section. We can also type in this box, but if we have a large number of values, it is a good idea to copy them from the progress bar. It saves manual efforts and time as well:

generate pivot output columns

Click on Generate Columns Now, and it gives following message about the output column results:

Column output results

Click OK, and you can see these columns in the existing pivoted output columns as shown below:

Existing pivoted output

Execute the package, and you can see Pivot data from the data viewer window:

View Pivot data using SSIS Pivot transformation

In the next section, we will use SSIS Unpivot transformation as well. Let’s use an OLE DB destination and add the Pivot data in a SQL table.

Drag OLE DB Destination from SSIS toolbox:

Drag an OLE DB destination

In the destination, specify the table name for Pivot data along with SQL instance connection:

OLE DB destination editor

Click on Mappings and verify mapping between source and destination columns:

Verify source and destination mapping

Click OK and execute the package. Verify the Pivot data in the SQL table:

Verify Pivot data

SSIS Unpivot Transformation

Sometimes we get Pivot data, and we require to get the original data. We require performing reverse Pivot transformation. We also call it an Unpivot transformation.

In the new SSIS package, drag OLE DB Source and Unpivot transformation.

Configure the OLE DB source for the SQL table in which we inserted Pivot data in the above section:

SSIS Unpivot transformation

Double-click on Unpivot and configure it:

Unpivot transformation editor

  1. Available Input columns: In this column, we select the columns to Unpivot. You can also notice a column Pass-Through. Click on Pass-Through for remaining columns. If we do not click on the checkbox for Pass-through, that column will not be available in the destination.

    In this example, we select the C_2005_OrderQuantity, C_2006_OrderQuantity, C_2007_OrderQuantity, C_2008_OrderQuantity as available input columns, and Name as a pass-through column

  2. Input Column: Once we select any column in the available input columns, it automatically appears as an input column
  3. Destination Column: Specify the destination column that will store the values
  4. Pivot Key Value: Here, we specify the Pivot key value. It should be similar to the Pivot key we used in the Pivot transformation. By default, it takes a similar value of input and pivot key columns
  5. Pivot Key-value column name: We can specify the column name for the pivot key as per our requirement

In the following screenshot, we can see input and pivot key-value columns:

Unpivot configuration

Make the changes as per the following screenshot:

Specify inputs

Pivot key value column name: In our original data, it should appear as the year, therefore, changes it to the respective year. For example, change c_2005_OrderQuantity to 2005.

Add an SSIS multicast transformation and enable data viewer between Unpivot and multicast connection. SSIS Upivot transformation SSIS package looks like the following screenshot:

SSIS configuration

Execute the SSIS package and verify the data. We can see the original data (Unpivot) in the data viewer:

Verify Unpivot data using SSIS Unpivot transformation

You can notice the Unpivot converts 266 rows to 613 rows. It is opposite to the pivot transformation in which we saw 613 rows conversion into 266 rows.

Conclusion

In this article, we explored SSIS Pivot transformation and SSIS Unpivot transformations for preparing Pivot and Unpivot data. We can easily use these transformations without any complex T-SQL knowledge.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views