The UNPIVOT Transformation in SSIS is used to convert the DE Normalized data into more normalized version, which is similar to SQL UNPIVOT. UNPIVOT Transformation acts exactly opposite to Pivot Transformation. Please refer Pivot Transformation in SSIS article to understand, How to Convert Normalized data to DE Normalized data.
Consider below data as our source data
UNPIVOT Transformation in SSIS Example
In this example We are going to convert the Pivot table present in the Employees database into normal 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 and it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, UNPIVOT Transformation from toolbox to data flow region as show 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 columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
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 UNPIVOT Transformation to configure it.
- Available Input Columns: Select the columns you want to UNPIVOT by checking the check boxes. Don’t forget to select the Pass Through option for the remaining columns. If you miss to select 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 column in the Available Input Columns Option will 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 will be stored as column values inside the Calendar Year column (Pivot Key Value Column Name). The default value is same as input column however, you can change as you like. We are fine 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 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 Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish our package designing UNPIVOT Transformation in SSIS. Let us run the package
Check the results in SQL Server Management Studio
Thank You for Visiting Our Blog