Home > Articles > SQL Server Articles > Step by Step working with Pivot component in SSIS.

Step by Step working with Pivot component in SSIS.

by Rahul Pyarelal   on Feb 01, 2017   Category: SQL Server  | Level: Beginner  |  Views: 897    |  Points: 100   
Like this article? Bookmark and Share:
In this article we will demonstrate complete step by step to work with Pivot.

First we will understand what work Pivot does? Pivot do interchanging of row data into columns data. In other words Pivot Load data and convert row into column.

Given the “SourcePivot” table in the data in the SQL Server as the following screen shot…

Pivot will work on “DayOfWeek” column it will transform. The output for this record set should be as under the following screen shot…

Drag and drop a “Data flow Task” in the control flow designer as under the following screen shot…

Double click on the “Data Flow Task” it go to the “Data Flow” design page.

Now drag and drop an “OLE DB source” from the SSIS Toolbox into the “Data Flow” design as the following screen shot…

Double click on the “OLE DB Source” and configure. In order to configure go to the “OLE DB connection manager” and create the connection as the following screen shot…

When you click to new then open the “configure OLE DB Connection Manager (1)”and click on the “NEW”. When you click on the “New”, “connection Manager (2)” Window will open and configure it, as the following screen shot…

When you configure the “configure OLE DB Connection Manage” than give the table name in the “Name of the Table or view” and click to OK.

Now go to the “Other transform” and dragging the pivot component from the SSIS Toolbox. Than connect to “OLE DB Source” to the “Pivot” transformation and configure as the following screen shot…

Pivot Key: Provide the column name which value you want to convert to columns. It is column which will contain values and this column will become new column name in the output.

Set Key: Provide the column which values will be grouped as a row value. It is a column in source data source, which contain group of input row that will get pivoted into one output row. The input data must be shorted on this column.

Pivot Value: Provide the column which has values. It is a column which contains the value which is going to be mapped into the new pivot output columns.

When you configure the “Pivot” it shows the Error as the following screen shot…

In order to solve the error, go to the “Pivot” component. Double click on the Pivot and go to the “Generate pivot output column from the values” and write the column name (what column you want.) than click on the “Generated column Now” as see the following screen shot…

Hit Generate Columns Now button as see the following screen shot…

Now go to the SSIS Toolbox and dragging the “OLE DB Destination” into the “Data Flow” design page. Than connect to “Pivot” to “OLE DB Destination” as the follow the screen shot…

Now configure the “OLE DN Destination”. Then go and give the name of the destination table as the following screen shot…

Then go to the “Mappings” and input & output columns name is not same. Join the C_Sunday_SalesAmount to Sunday, C_Monday_SalesAmount to Monday,
C_Tuesday_SalesAmount to Tuesday and C_Wednesday_SalesAmount to Wednesday as see the following screen shot…

Then press “F5”to run the application.

Output

Hope that reader has understood above step by step article where I have demonstrated working with Pivot component.

Also go through below MSBI project video which teaches SSIS from scratch: -



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


No response found, be the first to review this article.

Submit feedback about this article

Please sign in to post feedback

Latest Posts

1 ???????? ????????? New! Forum