在SSIS中将逗号分隔的字符串分成几行?

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.

我想基于第一个表的输入实现第二个表的输出。我想通过SSIS包来实现这一点。

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.

到目前为止，每当出现逗号(，)时，我尝试创建一个带有bypass错误的包，但这不起作用。也尝试过使用检查点，但也无法实现。

3 个解决方案

#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):

在数据流任务中，OLEDB源选择源类型为SQL命令，并使用以下命令(用表名替换Tablename):

;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

SQL小提琴演示

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
• 将多值列分割为多个记录

#2

You can try this

你可以试试这个

SELECT
    tbl.id,
    Splita.a.value('.', 'NVARCHAR(MAX)') [Group]    
    FROM
    (
        SELECT CAST('<X>'+REPLACE( [Group], ',', '</X><X>')+'</X>' AS XML) AS Col1,
             id

      FROM  Table1
    ) AS tbl
    CROSS APPLY Col1.nodes('/X') AS Splita(a)

here is the Fiddler link.

这是Fiddler链接。

#3

If you decide to split with TSQL on the source database, you can use STRING_SPLIT() on SQL server 2016 and onwards.

如果您决定在源数据库上使用TSQL进行分割，那么可以在SQL server 2016和以后的SQL server上使用STRING_SPLIT()。