The UNPIVOT Transformation in SSIS is used to convert the DE Normalized data into a more normalized version, which is similar to SQL UNPIVOT. The SSIS UNPIVOT Transformation acts precisely opposite to Pivot Transformation. Please refer to Pivot Transformation in SSIS article to understand, How to Convert Normalized data to DE Normalized data.
Consider the below data as our source data
UNPIVOT Transformation in SSIS Example
In this SSIS UNPIVOT Transformation example, We are going to convert the Pivot table present in the Employees database into a regular table.
STEP 1: Open BIDS and Drag and drop the data flow task from the toolbox to control flow and rename it as UNPIVOT Transformation
Double click on it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, UNPIVOT Transformation from the toolbox to the data flow region, as shown in the figure.
STEP 3: Double click on OLE DB source in the data flow region will open the connection manager settings and provides space to write our SQL statement. In this example, we are going to use the PIVOT Table in Employees Data Base as our source
STEP 4: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns.
Here we are selecting ProductID, Name, 2005, 2006, 2007 and 2008
Click ok and drag the blue arrow from the OLE DB Source to UNPIVOT Transformation to perform transformations on the source data.
STEP 5: Double click on the SSIS UNPIVOT Transformation to configure it.
- Available Input Columns: Select the columns you want to UNPIVOT by checking the checkboxes. Don’t forget to select the Pass Through option for the remaining columns. If you miss selecting the Pass Through option, then those columns will not be available for the destination. In this example, ProductID and Name columns are Pass Through columns, and 2005, 2006, 2007, 2008 are the columns we want to UNPIVOT.
- Input Column: All the selected columns in the Available Input Columns Option automatically added to this section. So, 2005, 2006, 2007, 2008 columns are the Input Columns
- Destination Column: Specify the name of the destination column where you want to store the values. Or data inside the Input Columns such as 2005, 2006, 2007, 2008.
- Pivot Key Value: Specify the name for pivot value, and these values stored as column values inside the Calendar Year column (Pivot Key Value Column Name). The default value is the same as the input column. However, you can change as you like. We are okay with the values 2005, 2006, 2007, 2008, so we haven’t changed these values.
- Pivot Key Value Column Name: Specify the name you wish to see for the pivot value column. Since our column data is 2005, 2006, 2007, and 2008 we changed the default Pivot Key Value to Calendar Year.
We named destination column as Order Quantity and Pivot Key-value column name as Calendar Year
STEP 6: Now, we have to provide the Server, database, and table details of the destination. So double-click on the OLE DB Destination and provide the required information.
Here we selected [SSIS Tutorials] database as our destination database and [UNPIVOT Transformation] table as our destination table.
STEP 8: Click on the Mappings tab to check whether the SSIS UNPIVOT Transformation source columns exactly mapped to the destination columns.
Click ok to finish our package designing UNPIVOT Transformation in SSIS. Let us run the package
Check the SSIS UNPIVOT Transformation results in SQL Server Management Studio