Parse CSV string into multiple columns and rows using SSIS

Introduction

In our previous blog we saw How to write data into CSV file in SSIS (GZip / Split). Now in this blog, we will Parse CSV string into multiple columns and rows using SSIS CSV Parser Transform can (Helpful to extract data from raw CSV string stored as database column or coming from other source). It also supports Pivot mode so you can convert single CSV string value into Rows.

In nutshell, this post will focus on how to Parse CSV string into multiple columns and rows using respective CSV Parser Transform.

Prerequisite

1. First, you will need to have SSIS installed
2. Secondly, make sure to have SSDT
3. Finally, do not forget to install ZappySys SSIS PowerPack

How to Extract Data from SQL Server Table and Parse string into multiple columns and rows.

Let´s start with an example. In this SSIS CSV Parser Transform example, we will Parse database column string into multiple columns and rows.

1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
   

   Drag and Drop : SSIS Data Flow Task from SSIS Toolbox

2. Furthermore, drag and drop the OLE DB Source.
   

   OLE DB Source – Drag and Drop

3. Double click on OLE DB Source for configure it and click on OK.
   

   Configure OLE DB Source Editor

4. Now drag and drop CSV Parser Transform and connect it with the ole db source.
5. Double click on CSV Parser Transform and select that column from the drop down and enter sample data and click on Preview.
   

   CSV Parser Transform Preview

6. That’s it, now you can use that data in desire destination.
   

   CSV Parser Transform

SSIS CSV Parser Transform – Pivot Option (Normalize CSV string)

Pivot Option is enabled you will see two extra columns in the output (Pivot_Name and Pivot_Value).

Property name will appear in “Pivot_Name” column and property value will appear in ”Pivot_Value’’ column. This option is very helpful when your data has dynamic number of attributes and you want to store them as rows rather than columns. Parent level.

SSIS CSV Parser Transform – Pivot Option (Normalize CSV string)

SSIS CSV Parser Transform – Pivot Option (Runtime view – Normalize CSV string)

Conclusion

After all, we saw you how to Parse string into multiple columns and rows using SSIS CSV Parser Transform from SQL Server Table using OLE DB Connection.  To explore many other scenarios not discussed in this article download SSIS PowerPack from here (includes 70+ Components).

References

Finally, you can use the following URL for more information.

• Help File: CSV Parser Transform