Split comma separated string columns 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.

Answer 1

Assuming your source table have only a two element CSV, we could try a union approach here:

SELECT
    ID,
    SUBSTRING([Group], 1, CHARINDEX(',', [Group]) - 1) AS [Group],
    SUBSTRING([Category], 1, CHARINDEX(',', [Category]) - 1) AS Category
FROM yourTable
WHERE
    [Group] IS NOT NULL
UNION ALL
SELECT
    ID,
    SUBSTRING([Group], CHARINDEX(',', [Group]) + 1, LEN([Group])),
    SUBSTRING([Category], CHARINDEX(',', [Category]) + 1, LEN([Category]))
FROM yourTable
WHERE
    [Group] IS NOT NULL
UNION ALL
SELECT ID, [Group], Category
FROM yourTable
WHERE [Group] IS NULL
ORDER BY
    ID,
    [Group];

Demo