Split record with start- and enddate into seperate records per date

Case
If you have records with a start- and enddate and you want to split them into separate records per date, the Script Component might be a solution for you.

Starting point

Id	Startdate	Enddate
1	01-21-2010	01-28-2010
2	09-26-2010	10-02-2010

Desired situation

Id	Factdate
1	01-21-2010
1	01-22-2010
1	01-23-2010
1	01-24-2010
1	01-25-2010
1	01-26-2010
1	01-27-2010
1	01-28-2010
2	09-26-2010
2	09-27-2010
2	09-28-2010
2	09-29-2010
2	09-30-2010
2	10-01-2010
2	10-02-2010

Solution
1) Add a script component to your dataflow.

Script component (transformation)

2) Select all columns you need and select ReadOnly (default).

ReadOnly Input columns

3) Now make sure that your task is Asynchronous (number of records in isn't equal to the number of records out) by selecting "None" at SynchronousInputId.

Asynchronous

4) Copy all input columns to the output columns, but the StartDate and EndDate will be replaced by one date column named FactDate. Make sure that the datatypes match.

Copy columns

5) Now the script (I used VB.net in this example). SSIS will generate two methods named:  Input0_ProcessInputRow and CreateNewOutputRows. You can remove the last one because we only need Input0_ProcessInputRow. First get the start- and enddate, then loop through the dates and add a row for each date:

' Split a record with a start- and enddate into separate records per date
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    ' Method that will be started for each record in you dataflow
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Declare variables to use them in the loop
        Dim StartDate, EndDate as Date

        ' Get StartDate from dataflow
        StartDate = Row.StartDate

        ' Get EndDate from dataflow. You could add some default value if the EndDate is unknown.
        If Row.EndDate_IsNull Then
            EndDate = DateTime.Now.Date
        Else
            EndDate = Row.EndDate
        End If

        ' Loop through dates
        While StartDate <= EndDate
            Me.Output0Buffer.AddRow                ' Add a new record
            Me.Output0Buffer.FactDate = StartDate  ' Fill the new created FactDate column
            Me.Output0Buffer.Id = Row.Id           ' Fill all other columns copied from the input columns
            StartDate = StartDate.AddDays(1)       ' Add one day to go to the next date
        End While
    End Sub

End Class

6) Now add a target and run the package to see the result:

2 rows => 15 rows

Note: There are other ways to fix this case, but this is one is quite easy and quick.