SSIS - How to Insert from Flat File to OLE DB with date range?

Question

I have a flat file source with columns that have start and end dates per row, and the table I am attempting to insert into only has one date column, which means I have to insert 1 row per week from the start date until the end date.

Sample FF Source:
Col1,StartDate,EndDate,Col4
1234,7/10/2018,28/10/2018,1.000

Rows to be inserted into table:
+------+------------+-------+
| Col1 | Date | Col4 |
+------+------------+-------+
| 1234 | 7/10/2018 | 1.000 |
| 1234 | 14/10/2018 | 1.000 |
| 1234 | 21/10/2018 | 1.000 |
| 1234 | 28/10/2018 | 1.000 |
+------+------------+-------+

Answer 1

This is how you would take Nicks suggestion and implement:

--This just replicates your load to a staging table
declare @t table (Col1 int,StartDate date,EndDate date,Col4 money)
insert into @t
values
(1234,'10/7/2018','10/28/2018',1.000)

--This will be your insert into final
select Col1,EndDate as [Date],Col4
from @t 

union all

select t.Col1,a.Date,t.col4
from @t t
cross apply (select * 
             from dDate 
             where [Date] >= t.StartDate --This includes start date
                 and [Date] < t.EndDate --This does not include ED, I did this to avoid result not ending on same day of the week
                 and [WeekDay] = datepart(dw,t.StartDate) --Weekly records starting on SD and progressing weekly
            )a
order by 1,2 -- Just for your viewing pleasure

Results:

Col1    Date        Col4
1234    2018-10-07  1.00
1234    2018-10-14  1.00
1234    2018-10-21  1.00
1234    2018-10-28  1.00

This assumes you have a DateDimension or "Calendar Table" with fields Date (representing the calendar date) and Weekday (Representing the numerical value of day of the week).

Answer 2

Here is a separate answer on how to do it in SSIS using a script task:

1. In Data Flow add a source reading your flat file
2. Add a Script component and select transformation (connect to source)
3. Go to input and select all inputs as read only
4. Go to input/output and add a new output and call it New
5. Add your new columns Col1,Date,Column (with data types)

6. Go to script and enter it and paste in the following code

   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       int daysToAdd = 0;
       DateTime SD = Row.StartDate;
   
       while (SD.AddDays(daysToAdd) < Row.EndDate)
       {
           NewBuffer.AddRow();
           NewBuffer.Col1 = Row.Col1;
           NewBuffer.Date = SD.AddDays(daysToAdd);
           NewBuffer.Column = Row.Col4;
   
           daysToAdd = daysToAdd + 7;
       }
   
       //Add the end date
       NewBuffer.AddRow();
       NewBuffer.Col1 = Row.Col1;
       NewBuffer.Date = Row.EndDate;
       NewBuffer.Column = Row.Col4;
   
   }
   

You will now have a new output called "New" that transformed your single line into weekly rows between start and end dates.