THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

The SSIS Pivot Transformation

The Pivot transformation is one of the most useful transformations in SSIS, and also the biggest pain to configure. It comes with one editor, the Advanced Editor, so no pretty GUI here. Let’s walk though an example and hopefully it will help you learn how to use pivot. First off, let’s define the source data and the destination of our pivoted data. We will worked from a small set of survey data where each person was asked four questions, see the table below.

Question

Answer

SurveyID

Name

EricJohnson

1

Height

5'11"

1

Favorite Color

Red

1

Age

31

1

Name

Josh Jones

2

Height

5'8"

2

Favorite Color

Black

2

Age

34

2

Name

Eric Beehler

3

Height

5'10"

3

Favorite Color

Green

3

Age

35

3

As you can see, there are 3 surveys, each with four questions. We want to pivot this data so that there is one row of data for each survey and the answers are in columns, like the table below.

SurveyID

Name

Height

FavoriteColor

Age

1

EricJohnson

5'11"

Red

31

2

Josh Jones

5'8"

Black

34

3

Eric Beehler

5'10"

Green

35

This is where our Pivot Transformation comes into play. Each source column needs to have a PivotUsage assigned to it in order to tell SSIS how to handle the column during the pivot. The valid values are as follows.

  • 0 - The column is not pivoted
  • 1 - The column is part of the set key. This identifies the grouping of data. In our example, SurveyID will be a 1 to define one row for each survey.
  • 2 - The column is a pivot column. The data in this column will be used to provide the new column names. In our example, each question will become a column, so Question will have a value of 2
  • 3 - The values from this column are placed in columns that are created as a result of the pivot. In our case, each Answer will be placed in the new question columns, so Answer will have a value of 3.

Now that we have the PivotUsage values, we can configure the Pivot Transformation. When you open the Pivot transformation, the first thing you need to do is define the input columns that you want to work with. This is doen on the Input Columns tabs, as shown. As you can see, we have selected to work with Question, Answer, and SurveyID. We are ignoring the id column as it is a tracking column in the source table and not needed in our destination.

clip_image002

Next we switch to the Input and Output Properties tab. Here we need to expand Pivot Default Input and then Input Columns. Now we need to set the PivotUsage value for each column as we previously discussed. Question will be a 2, Answer will be 3 (as shown), and SurveyID will be 1.

clip_image004

Now here is where it gets a little weird. We have to add columns to output for each column that will exist AFTER the pivot. In our case, we will have the five columns we saw earlier in our example destination. To add the columns, expand Pivot Default Output and select Output Columns. Next, click the Add Column button 5 times. Then you will need to go through each column and set up a more logical name than the default of Column, Column 1, etc… Select each column and change the Name property so you end up with five columns named SurveyID, Name, Height, FavColor, and Age, as shown below (Note that I called the Favorite Color column FavColor to save space).

clip_image006

So now we have an input and an output, now we need to connect the two. This is done by setting the SourceColumn property on each output column to the LineageID property of the correct input column. This tells the transformation where each output columns will get its data. Obviously, the first output column, SurveyID, comes from SurveyID. The value for the other four output columns all come from Answer input column. So for SurveyID, we fine the input column LineageID on the property page here:

clip_image008

Now you will need to write down that value or remember it and then manually enter it into the SourceColumn property of the SurveyID output column here:

clip_image010

Repeat this step and enter the LineageID from the Answer input column into each of the other output column’s SourceColumn property.

We are almost there, but we have one more step. For each of our new output columns, Name, Age, FavColor, and Height we need to tell them which values in the Question column signify that the data belongs in each column. This is done by setting the PivotKeyValue for each of these columns to the actual text in the Answer column that’s means this answers is for the question contained in this column. So for the Name column, we enter “Name” into the PivotKeyValue, as shown. This tells the transformation to take the value from the Answer input column and place it in our Name output column anytime it encounters the text “Name” in the Question column.

clip_image012

That’s it, now we can work with the pivoted data in our SSIS package just like any other data set. I know that’s a lot and it is very confusing, but if you play with it a few times you will get it. Good luck and happy coding.

Published Wednesday, October 20, 2010 12:47 PM by ejohnson2010
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mangutha said:

I getting this error when I followed you steps.

Error: 0xC0202090 at Data Flow Task, Pivot [29]: The pivot key value " Name" is not valid.

Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Pivot" (29) failed with error code 0xC0202090 while processing input "Pivot Default Input" (30). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\Users\Public\SSIS Testing\pivot.txt" has ended.

Information: 0x402090DD at Data Flow Task, Flat File Destination [66]: The processing of file "C:\Users\Public\SSIS Testing\Pivot_results.txt" has ended.

Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "Flat File Destination" (66)" wrote 0 rows.

Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

December 8, 2010 4:34 PM
 

Mangutha said:

Iam getting this below error when I followed your steps.

Error: 0xC0202090 at Data Flow Task, Pivot [29]: The pivot key value " Name" is not valid.

Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Pivot" (29) failed with error code 0xC0202090 while processing input "Pivot Default Input" (30). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\Users\Public\SSIS Testing\pivot.txt" has ended.

Information: 0x402090DD at Data Flow Task, Flat File Destination [66]: The processing of file "C:\Users\Public\SSIS Testing\Pivot_results.txt" has ended.

Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "Flat File Destination" (66)" wrote 0 rows.

Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

December 8, 2010 4:36 PM
 

Vasulu said:

Its working...

Thannks Eric Johnson

Keep posting

September 19, 2011 11:55 AM
 

mansi said:

Thanks Eric............

you brought a smile....

September 27, 2011 5:32 AM
 

Bhargav Kandala said:

Eric,

I followed the exact step by step but am getting the following error:

ackage Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [Pivot [689]]: Output column "Renewal License Accepted" (714) cannot be mapped to PivotKey input column.

Error at Data Flow Task [DTS.Pipeline]: "component "Pivot" (689)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

June 5, 2012 1:35 PM
 

Kamakshi Suram said:

Hi,

I too getting error as " Output column " Name" Cannot be maaped to input column.

Can anyone please suggest me.

June 26, 2012 5:40 AM
 

manish said:

i also getting same error

August 29, 2012 7:34 AM
 

guest said:

I am getting the same error. Output column cannot be mapped to input column. Any suggestion how to rectify this.

November 8, 2012 6:59 PM
 

manideep said:

can anyone post the solution for above mentioned error code 0xC0202090

June 4, 2013 3:04 AM
 

kaleshavali said:

ji

June 24, 2013 7:42 AM
 

JustPivoted said:

Eric,

Thanks for the detailed instructions, was very helpful.

As I am SSIS noob, took a little while to figure out all this shows up in the AdvancedEditor window.

Thanks Again.

August 23, 2013 1:18 PM
 

john grindel said:

can this be used in reverse?  take a flat row from database and map to multiple rows in outbound flat file with conditional rows?

header

documentation

details  conditional to value of column X greater then 0

details  conditional to value of column Y greater then 0

details  conditional to value of column Z greater then 0

And this will repeat many time in the file no Trailer record.

thanks

john

October 31, 2013 12:07 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement