SSIS Merge While?

Question

I have two datasets. One is a dimension table with data similar to the below sample

Table1

Key    StartDate     EndDate       Name       Data_1
1      2017-01-01    2017-01-03    Billy G    58uf
2      2017-01-10    2017-01-12    S Dogg     4940

I also have a standard Date Dimension called dbo.DimDate. How can I Merge the date dimension onto the Table1 so that Table1 contains a record for each day between StartDate and EndDate for each record?

The desired result is something like this:

Table2 

Key    StartDate     EndDate       Name       Data_1    Day
1      2017-01-01    2017-01-03    Billy G    58uf      2017-01-01
1      2017-01-01    2017-01-03    Billy G    58uf      2017-01-02
1      2017-01-01    2017-01-03    Billy G    58uf      2017-01-03
2      2017-01-10    2017-01-12    S Dogg     4940      2017-01-10
2      2017-01-10    2017-01-12    S Dogg     4940      2017-01-11
2      2017-01-10    2017-01-12    S Dogg     4940      2017-01-12

This needs to be accomplished in an SSIS package. I don't think a simple MERGE JOIN will work because 1) The Date dimension does not have a key that matches anything in Table1, and 2) I need it to add a Day for every date BETWEEN StartDate and EndDate

If I do an execute SQL Command, then how do I get it to pull from two separate tables?

the last record from your desired result, why the `EndDate` is '2017-01-11' not '2017-01-12'? — LONG, Mar 16 '17 at 18:22
If there is no common column (or key) to join, cannot use `Merge Join`, I will think about an alternate way to do this — LONG, Mar 16 '17 at 18:24

score 0 · Answer 1 · answered Mar 17 '17 at 13:20

0

You can do this with a Lookup. Your main source component will be the date dimension, and in the lookup, you'll get the row of Table1 where the date dimension's date is in the start/end range, and add those columns to your dataflow.

answered Mar 17 '17 at 13:20

Tab Alleman

31,483
7
36
52

score 0 · Accepted Answer · edited May 23 '17 at 12:25

0

I solved the issue by using a simple OLE DB source which executed the SQL command I copied from another post on stackoverflow ( seen here: SQL query to convert date ranges to per day records)

Basically just copied that, adjusted table names, and then pasted in the Source properties

edited May 23 '17 at 12:25

Community

1
1

answered Mar 17 '17 at 14:03

SUMguy

1,505
4
31
61

SSIS Merge While?

2 Answers2