SSIS tutorial: How to pivot columns to rows

SSIS Tutorials
,
1

Introduction

In this article, we will guide you through the process of pivoting columns to rows in SQL Server Integration Services (SSIS) using any ZappySys source component. Pivoting is a powerful transformation that reshapes data for more efficient reporting and analysis. By converting columns into rows, you can simplify complex datasets, making them easier to work with in SQL Server, Excel, or other business intelligence tools.

Prerequisites

Steps

  1. Drag and drop a Data Flow Task onto the Control Flow pane. Double-click the task to open the Data Flow tab.

  2. In the Data Flow tab, add a ZappySys Source component. For this tutorial, we will use the JSON Source component to import JSON data.

  3. Enter the URL or path to a valid JSON file. For this example, we will use the following JSON data:

    [
  {
    "product": "Laptop",
    "sales": {
      "January": 1000,
      "February": 1200,
      "March": 1500,
      "April": 1300,
      "May": 1100
    }
  },
  {
    "product": "Smartphone",
    "sales": {
      "January": 2000,
      "February": 2200,
      "March": 2500,
      "April": 2400,
      "May": 2300
    }
  },
  {
    "product": "Tablet",
    "sales": {
      "January": 800,
      "February": 900,
      "March": 950,
      "April": 1100,
      "May": 1200
    }
  }
]

  4. In the Filter option, enter $.sales and click Preview. This will return a table with the product names and months.

    Normal example
    Normal example828×745 45.3 KB

  5. To pivot the data into rows showing the product, month, and sales values, navigate to the Pivot Columns to Rows tab and check the option Enable Pivoting of child-level properties.

  6. After enabling pivoting, click Preview again to see the updated data format, where each row shows a product, month, and sales value.

    Pivot Example
    Pivot Example828×745 53.2 KB

Conclusion

Pivoting columns to rows in SSIS allows you to reshape your data, making it easier to analyze and report on. This technique is beneficial for both simple and complex datasets, simplifying the process of working with SQL Server, Excel, and other SSIS-compatible tools.

Explore the SSIS PowerPack for more information and download and install it to start building smarter, faster, and more scalable solutions

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.