How to parse data, split into multiple rows, and save to a flat file using SSIS?

Question

I have source data as follows:

ID  Data
1   text text text
2   text text text

In SSIS, I need to make a transformation which will result in

ID  Data
1   text
1   text
1   text
2   text
2   text
2   text

The destination file needs to be a flat file. Is it possible to do this transformation? How? I tried a Derived column with ID + REPLACE((DT_WSTR,4000)Data," ","\n"), but that seems to be an incorrect approach.

Agreed: not derived column, because you want new *rows*, not new columns. (Aside, FYI: REPLACE's first parm is a column name or string literal, not a datatype, and DT_WSTR is an SSIS datatype.) I suggest splitting in SQL rather than SSIS, for faster development. If it's **always 3** text blocks -- then you could simply do 3 `INSERT` commands to a table, then use the final table as your source. If it's not always 3 text blocks, then take a look at the answer to this SO question: http://stackoverflow.com/questions/6392340/sql-server-2008-split-multi-value-column-into-rows-with-unique-values — Doug_Ivison, Feb 20 '14 at 15:26
It looks like you reposted this question, here: http://stackoverflow.com/questions/21911117/sql-separate-row. In the future, rather than take people's time on 2 questions... please **edit** the first question you posted, or leave comments on it. — Doug_Ivison, Feb 20 '14 at 15:31
@Doug_Ivison: I don't agree with your comment - the other question is asking about a different technology - SQL vs SSIS. — Mike Honey, Feb 21 '14 at 02:30
I worked out the problem with derived column and replace string function + guide at http://social.msdn.microsoft.com/Forums/sqlserver/en-US/34ce1b87-fee7-48c9-b3aa-9615de138bbc/how-to-replace-multiple-blanks-into-one?forum=sqlintegrationservices . Thanks guys for your feedback! — DNac, Feb 21 '14 at 10:21

DNac · Accepted Answer · 2014-02-24T11:57:16.280

2

I worked out this problem with the Derived Column and it's REPLACE string function so the spaces in texts are replaced by | + guide at Split multi value column into multiple records

edited Feb 24 '14 at 11:57

answered Feb 21 '14 at 10:23

DNac

2,663
8
31
54

Glad you resolved it! For the sake of anyone finding this page... could you share how you split each row, into multiple rows (like you showed in your question)? The link you've given has a good examples of REPLACE, but in all its examples, one row still produces only one row. – Doug_Ivison Feb 24 '14 at 03:10
1

Thanks for notice. I attached wrong link by accident. Given the link in my answer, you can see how the work has been done. It provides full guide. Should you need to know more, please let me know. – DNac Feb 24 '14 at 11:58
Very interesting... and useful: adding a Script Component, with `Output0Buffer.AddRow()` in the script. – Doug_Ivison Feb 24 '14 at 12:18
Yes, I found it useful as well. Clever solution which works as it should. – DNac Feb 24 '14 at 12:49

score -2 · Answer 2 · answered Feb 21 '14 at 02:29

-2

In SSIS this is achieved using the UnPivot Transformation:

http://technet.microsoft.com/en-us/library/ms141723.aspx

answered Feb 21 '14 at 02:29

Mike Honey

14,523
1
24
40

The data to split is in a single column therefore the UnPivot cannot be used. The Script Component mentioned bij DNac will work for this situation... – Joost Oct 21 '16 at 11:26

How to parse data, split into multiple rows, and save to a flat file using SSIS?

2 Answers2