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.

Improve this question
3
  • 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. 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. For further information, please see how to ask good questions, and take the tour of the site. Commented Feb 11, 2018 at 19:19
  • Just get a string splitting function like DelimitedSplit8k and you just need simple select statement Commented Feb 11, 2018 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 Commented Feb 11, 2018 at 19:35

3 Answers 3

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

Comments

0

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

Improve this answer

1 Comment

The OP added ssis-2012 tag which means that he is using sql server 2012

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.