Copy a single source row to multiple destination rows by matching a common 'Date' field with SSIS

Question

Here's the scenario: I'm an SSIS virgin, but I found an excuse to start playing with it. I have a single Excel source dataset in the following format, with each row representing one month (the last day of the month):

[Date]       [Value]
4/30/2008    3.38
5/31/2008    3.65
6/30/2008    3.97
...

My destination is a table that has a row for each day of the month, where the [Value] column is my empty column I need to fill with the values from the Excel document:

[Date]       [Value]   [Other columns with distinct data]
4/01/2008    NULL      .....
4/01/2008    NULL
4/01/2008    NULL
4/02/2008    NULL
4/02/2008    NULL
4/02/2008    NULL
4/03/2008    NULL
4/03/2008    NULL
4/03/2008    NULL
...

I need to copy the single value for each month in the Excel document for each day of the corresponding months values in the destination table, such that the previous table would end up looking like:

[Date]       [Value]
4/01/2008    3.38
4/01/2008    3.38
4/01/2008    3.38
4/02/2008    3.38
4/02/2008    3.38
4/02/2008    3.38
4/03/2008    3.38
4/03/2008    3.38
4/03/2008    3.38
...

You might be thinking "why do you want the value duplicated across rows?", but this is just a backfilling of data -- future entries are made using a web application that save them in bulk, but individual day entries can be tweaked or edited manually.

So, my question is: what might the components be between my Excel Source and my SQL Server Destination in my SSIS package? I know what I need to accomplish:

• For each Date in the destination dataset, I want to look up in the Excel Source the matching month in its Date column and find the corresponding Value
• Next, I need to insert the Excel Value into the Value column of my SQL Server Destination.
• And repeat...

Does this even seem worth using SSIS for? It's a one-time gig, but like I said, I thought this would be a good opportunity to dabble in SSIS. In the meantime, I'm probably just going to throw together a quick macro in Excel to generate some UPDATE statements for me.

Thanks!

Answer 1

Get the contents of the Excel sheet into a temp table in your SQL DB (temp table: #excelData).

And, then write an update statement

UPDATE mainTable, #excelData
SET mainTable.Value = #excelData.Value
WHERE datepart(yy, mainTable.Date) = datepart(yy, #excelData.Value)  
and datepart(mm, mainTable.Date) = datepart(mm, #excelData.Value)