Unpivot Transformation with Example- SSIS
This blog post is just to give a step by step instruction to work with Unpivot Transformation in SSIS.
Unpivot Transformation is used to convert unnormalized dataset into normalized dataset. Database Normalization.
For instance, we have got a data as show in the below image and we want to normalize the data to make the user table compact.
In this dataset, Column 1 contains Customer names, column 2 to 6 contains product names and each data cell represents the quantity purchased by the customer. But to store this data in the RDBMS, we need to convert this dataset into normalized structure. So, In normalizes structure this data will looks like this.
Now, we are going to implement this scenario in using SSIS.
CREATE TABLE [dbo].[tblPurchase](
[Customer] [nchar](10) NULL,
[Ham] [nchar](10) NULL,
[Soda] [nchar](10) NULL,
[Milk] [nchar](10) NULL,
[Beer] [nchar](10) NULL,
[Chips] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tblPurchase]
(‘Kate’,’2′, ‘6’, ‘1’, ’12’,NULL),
Step 1: Create a new package
Step 2: Add a Data Flow Task in to package and Go to Data Flow environment
Step 3: Add a OLE DB source task to configure to read the table.
Step 4: Add Unpivot transformation. This task is used to convert unnormalized dataset to normalized dataset. Unpivot Transformation Editor.
Notice that the customer column is removed from the pivot input column and setup the output column as quantity and the product column as pivot key. So, we will get the output of Product and its quantity for each customer in multiple rows.
Note: All null value quantity rows will be removed by this transformation.
Step 5: Use the OLE destination column to store the unpivoted output in the normalized table. In this example, I am using the union all transformation as the destination and added data viewer to browse the output in the pipeline.
Step 6: Execute the package
This output show that the unpivoted dataset is in normalized structure.
Now, You can easily normalize any unnomalized dataset using Unpivot Transformation task in SSIS. I hope you have enjoyed reading this article. So, please share your thoughts and question in the comment section.