Split comma separated string into several rows in SSIS?

Question

I want to achieve the output in 2nd table based on the input from the first table. I want to do this via a SSIS package.

So far I tried creating a package with bypassing error whenever there comes a comma (,), but that didn't work. Also tried using checkpoints, couldn't achieve it that way as well.

Answer 1

1st Method - You can achieve this using an SQL statement

In the Data Flow Task, in the OLEDB Source select the source type as SQL Command and use the following command (replace Tablename with your table name):

;WITH tmp(ID,  DataItem, [Group]) AS(
SELECT ID, LEFT([Group], CHARINDEX(',', [Group] + ',') -1),
       STUFF([Group], 1, CHARINDEX(',', [Group] + ','), '')
FROM [Tablename]

UNION ALL

SELECT ID,  LEFT([Group], CHARINDEX(',',[Group]+',')-1),
       STUFF([Group], 1, CHARINDEX(',',[Group]+','), '')
FROM tmp
WHERE [Group] > ''
)

SELECT ID,  DataItem
FROM tmp
ORDER BY ID

SQL Fiddle demo

References

• Turning a Comma Separated string into individual rows

---

2nd Method - Using Script Component

You can refer to this link for a detailed answer:

• Split multi value column into multiple records