Pivot component in SSIS (SQL Server Integration Services)

Pivot component is Data Flow component available under Transformation of SSIS(SQL Server Integration Services). Configuring it improperly may cause difficulties in working with component. We at Mumbai MSBI training give enough learning and practice for many such SSIS component.

Error:

The error occurred during the pivoting process of the records. The records in the SQL are as follows,

1

Here we can see that the records are not sorted and also it is not properly grouped.

Therefore the error has caused on Pivot component,

2

3

The error says that the value violated the integrity constraints of the column and here constraints means that the values in the columns are not sorted.

Solution:

The solution is to add a new component named “Sort” and in this component define on which column field we have to do sorting.

4

Connect output of “OLE DB Source” component as input to “Sort” component and then connect output of “Sort” component as input to “Pivot” component.

5

Now again re-configure the Pivot component by double clicking the component and opening its Editor window

6

Once Editor is open check mark on field “Month” in Available Input Columns and select “Sort Type” as “ascending”.

7

After configuring the component run the package by clicking on “Start” option available under Menu bar.

8

The result is shown here after adding and configuring the Sort Component.

9

Explaining each steps and getting practical’s done from the candidate is key benefit of attending MSBI training. With this tradition established among the candidates will help them to remember the topic learned.

Comments

comments

This entry was posted in MSBI Training in Mumbai and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *