Split single row into multiple rows in SSIS

Question

Hi.

Given an input of the format (currently a flat file):

Name, StartDate, EndDate

with sample data

like

Peter, 2010-09-01, 2010-09-04

I was wondering which SSIS task I could use to split this single row into a row per day: Peter, 2010-09-01 Peter, 2010-09-02 Peter, 2010-09-03 Peter, 2010-09-04

I know I can probably do it with a script task, but I thought there might be something built in?

Any help is very much appreciated.

Answer 1

When I needed to split up rows I used the multi-cast, but I had a fixed amount of rows (ie. make one row into three).

In your case I would go with a script component.

If don't want to script in SSIS you could also write the data to an SQL table, and write your own SQL stored procedure to return the rows you want.

Probably you could also do it with a SELECT statement. Just ask the same questions with SQL:

"how can I select all dates between two dates in SQL"

Detail Multicast

1. Connect a multicast to your datasource
2. drag the output to a "add column"
3. in the add column add an expresion that does "startdate +1"
4. repeat with connecting the next ouput from the multicast to a new "add column" where you do startdate+2
5. join all the add column outputs with a "union"

That way will only work if you have a small maximum duration between the dates, if you have 40 days you would need 40 multi cast.