split data row without using script component

Question

hi , 

  i have the sample  data like

Bundle of items
one, eleven
six, eleven
four, eleven
six, one
six, one, eleven

i need output like

Bundle of items
one

eleven

six

eleven

four

eleven

six

one

six

one

eleven

but i need to do same example for 10000 records

Answer 1

Why don't you want to use the script component? It's ideal for scenarios like this one.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 2

i just not want to write the c# code i need other task that performs the above scenario

Answer 3

You could add a rownumber column with a unique ID for every row, use the unpivot transform and then drop the rownumber column, but that seems more hassle to me than writing a few lines of C#.

What is your source? If it's Excel or Flat File, you'll need a script component to add the rownumber ;)

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 4

You need to write a stored procedure to do it.

Search for the "," and first make it as different column.

Then unpivot the coumn .

Regards,

YB

Answer 5

could u plz provide the example procedure  for that 

Answer 6

You need to write a stored procedure to do it.

Search for the "," and first make it as different column.

Then unpivot the coumn .

Regards,

YB

How exactly are you going to make the "," a "different column"? Especially since there can be multiple comma's in one line.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 7

there are multiple commas

Answer 8

Koen's correct. It doesn't like reading a file you can split them by comma..

---

Please vote if it's helpful and mark it as an answer!

Answer 9

flat file aS SOURCE

Answer 10

flat file aS SOURCE

I tested it, and you don't need a unique row number.
So just add a derived column that adds a column containing a constant value, for example 1.
This column will serve as the pivotcolumn.

After the derived column, add the unpivot component and unpivot the data using the newly generated pivotcolumn.
The destination column is the same for all unpivoted columns and will contain the actual values. The column names will be stored in another column, which you can dismiss.

The only obstacle is that you need to have a consistent number of columns, which should be no problem since you are using the Flat File Source. So, in your example, you need to have the following input:

Bundle of items
one, eleven, NULL
six, eleven, NULL
four, eleven, NULL
six, one, NULL
six, one, eleven

You can filter out the NULL values after the Unpivot with a conditional split.

---

MCTS, MCITP - Please mark posts as answered where appropriate.