{"id":7775,"date":"2019-08-17T06:49:12","date_gmt":"2019-08-17T06:49:12","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7775"},"modified":"2025-11-14T03:40:56","modified_gmt":"2025-11-14T03:40:56","slug":"parse-csv-string-multiple-columns-rows-using-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/parse-csv-string-multiple-columns-rows-using-ssis\/","title":{"rendered":"Parse CSV string into multiple columns and rows using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"SSIS<\/a>In our previous blog, we saw\u00a0how to write data to a CSV file in SSIS (GZip\/Split)<\/a>.<\/span>\u00a0In this blog, we will parse a CSV string into multiple columns and rows using the SSIS CSV Parser Transform<\/a> (helpful for extracting data from a raw CSV string stored as a database column or from another source).<\/span> It also supports Pivot mode, allowing you to convert a single CSV string value into Rows.<\/p>\n

In a nutshell, this post will focus on parsing a CSV string into multiple columns and rows using the CSV Parser Transform.<\/p>\n

 <\/p>\n

Prerequisite<\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n<\/ol>\n

    How to Extract Data from SQL Server Table and Parse a string into multiple columns and rows.<\/h2>\n

     <\/p>\n

    Let’s start with an example. In this SSIS CSV Parser Transform example, we will parse a database column containing a CSV string into multiple columns and rows.<\/p>\n

      \n
    1. First of all, drag and drop the Data Flow Task from the SSIS Toolbox and double-click it to edit.\n
    2. Furthermore, drag and drop the OLE DB Source.\n
    3. Double-click the OLE DB Source to configure it, then click OK.\n
    4. Now drag and drop the CSV Parser Transform, then connect it to the OLE DB source.<\/li>\n
    5. Double-click on CSV Parser Transform and select that column from the drop-down, enter sample data, and click on Preview.\n
    6. That’s it, now you can use that data in your desired destination.\n
      \"\"<\/a>

      CSV Parser Transform result<\/p><\/div><\/li>\n<\/ol>\n

      SSIS CSV Parser Transform – Pivot Option (Normalize CSV string)<\/h2>\n

      If Pivot Option is enabled, you will see two extra columns in the output (Pivot_Name and Pivot_Value).<\/p>\n

      Property name will appear in the “Pivot_Name” column, and property value will appear in the \u201dPivot_Value’\u2019 column. This option is beneficial when your data has a dynamic number of attributes and you want to store them as rows rather than columns\u2014parent level.<\/p>\n

      \"\"<\/a>

      SSIS CSV Parser Transform – Pivot Option (Normalize CSV string)<\/p><\/div>\n

      Conclusion<\/span><\/h2>\n

      After all, we saw how to parse a string into multiple columns and rows using the SSIS CSV Parser Transform from a SQL Server Table using an OLE DB Connection.\u00a0 To explore many other scenarios not discussed in this article, download SSIS PowerPack from here (includes 70+ Components)<\/a>.<\/p>\n

      References<\/span><\/h2>\n

      Finally, you can use the following URL for more information.<\/p>\n