3

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.

enter image description here

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Welcome to StackOverflow! Have you tried anything so far? StackOverflow isn't a free code-writing service, and expects you to [**try to solve your own problem first**](http://meta.stackoverflow.com/questions/261592). Please update your question to show what you have already tried, showcasing a **specific** problem you are facing in a [**minimal, complete, and verifiable example**](http://stackoverflow.com/help/mcve). For further information, please see [**how to ask good questions**](http://stackoverflow.com/help/how-to-ask), and take the [**tour of the site**](http://stackoverflow.com/tour). – Obsidian Age Feb 11 '18 at 19:19
  • Just get a string splitting function like [DelimitedSplit8k](http://www.sqlservercentral.com/articles/Tally+Table/72993/) and you just need simple select statement – James Z Feb 11 '18 at 19:34
  • 1
    I think there are 2 methods in SSIS: using **SQL Command as OLEDB Source** or using a **Script Component**, check my answer for more details – Hadi Feb 11 '18 at 19:35

3 Answers3

4

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

2nd Method - Using Script Component

You can refer to this link for a detailed answer:

Hadi
  • 36,233
  • 13
  • 65
  • 124
1

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.

Prabhath Amaradasa
  • 493
  • 2
  • 7
0

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

Martin Thøgersen
  • 1,538
  • 18
  • 33