Home > SSIS > Unpivot Transformation with Example- SSIS

Unpivot Transformation with Example- SSIS


This blog post is just to give a step by step instruction to work with Unpivot Transformation in SSIS.

Basics:

Unpivot Transformation is used to convert unnormalized dataset into normalized dataset. Database Normalization.

Example:

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.

pic1

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.

pic2

Now, we are going to implement this scenario in using SSIS.

Table Creation:

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 rows:

INSERT INTO [dbo].[tblPurchase]
([Customer],[Ham],[Soda],[Milk],[Beer],[Chips])
VALUES
(‘Kate’,’2′, ‘6’, ‘1’, ’12’,NULL),
(‘Fred’,NULL,NULL,’3′,’24’,’2′),
(‘Robert’,NULL, ‘1’,’2′,’3′,’1′),
(‘John’,’3′,’4′,NULL,’30’,’2′),
(‘Dan’,’2′,’2′,’3′,’1′,’4′);

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.

pic3

Step 4: Add Unpivot transformation. This task is used to convert unnormalized dataset to normalized dataset. Unpivot Transformation Editor.

pic4

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.

pic5

Step 6: Execute the package

pic6

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.

Reference link

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: