How to Pivot Key Column as Columns and Value Column as Rows Using SSIS

hardik · June 21, 2024, 11:08am

In this tutorial, we will walk through the process of transforming JSON data in SSIS to pivot a key column as columns and a value column as rows. This involves using the JSON Parser Transform and Pivot Transformation in ZappySys.

Step 1: JSON Parser Transform Configuration

Open the JSON Parser Transform Component:

In your SSIS package, drag and drop the ZappySys JSON Parser Transform component onto the Data Flow Task.

Settings Tab Configuration:

Select Input JSON Column: Choose the column that contains the JSON data, for example, attrs.attr [Excel Source].
Include all upstream columns to downstream: Check this option to ensure all upstream columns are included.

Enter Sample JSON String:

Add a sample JSON string in the provided text box to assist in metadata parsing.

[{"name":"Customer_Number","type":"6","qual":"Customer_Number","value":"7525.0"}]

Preview:

Click the Preview button to ensure that the JSON is parsed correctly.

Step 2: JSON Parser Transform Columns Selection

Columns Tab Configuration:

Select Available Columns: Check the columns you need. In this example, select name and value.
Map these columns accordingly: name as name and value as value.

Step 3: Data Preview

Preview the Parsed Data:

The data should be displayed in a tabular format with columns and rows. Ensure the name and value columns are correctly populated.

Step 4: Pivot Transformation

Add the Pivot Transformation Component:

Drag and drop the Pivot Transformation component onto the Data Flow Task.
Connect the output of the JSON Parser Transform to the Pivot Transformation.

Configure Pivot Transformation:

Pivot Key Column: Select the column that will be transformed into multiple columns, such as name.
Pivot Value Column: Select the column that contains the values, such as value.
Set Key Column: Select the column that contains the unique value for a group of input rows, such as id.
Generate pivot output columns from values: Enter the columns that contain the unique Keys, and values of the name column.
Generate Columns Now: After configuring all these fields, click on the Generate Columns Now to generate the columns.

Step 5: Execution and Validation

You’re done with the configuration, Below is the sample screenshot after the execution of the SSIS package.

To include all the source columns in your data flow, you can use the Multicast, Sort, and Merge Join components. Here’s a refined description of how to set it up:

Multicast Component: Use this component to create multiple copies of the source data. This allows parallel processing of the same data set.
Sort Component: Apply the Sort component to each branch of the multicast output. This ensures that the data is sorted before merging, which is crucial for the Merge Join component to function correctly.
Merge Join Component: Use this component to merge the sorted data streams back together. This will include all the columns from the source data in the final output.

Below is a sample screenshot of the package data flow design:

Source → 2. Multicast → 3. Sort (for each multicast output) → 4. Merge Join (to combine sorted outputs).

By following these steps, you ensure that all source columns are included and properly processed in your data flow.

Summary

This article provides a step-by-step guide to parsing JSON data and pivoting it using ZappySys components in SSIS. By following these instructions, you can efficiently transform JSON data, pivoting a key column as columns and a value column as rows.

These steps include configuring the JSON Parser Transform, selecting necessary columns, previewing parsed data, using the Pivot Transformation, and validating the final output.

Feel free to customize the JSON strings and column mappings based on your specific requirements. Happy data transforming!