Nick, you can do this several different ways:

1) In the data flow, you could set up two different data sources (OLE DB data source or ODBC data source) for the two tables, and then use a MERGE JOIN transformation in the data flow to join them together. Note that the MERGE JOIN requires that both inputs be sorted by the same column.

From there, you'll have one pipeline flow. At that point you can use an Excel destination

2) back in SQL server you could join the two tables and then use an Excel destination in the SSIS data flow.



Now, these might help....here's a webpage from when I taught SSIS. (Don't laugh at the website, it was a quick and dirty way to provide a portal for students to get at video files).

http://kevin_s_goff.typepad.com/setfocusadditionalbi/ssis-1/

There are 2 videos in particular that might help you out. They are towards the bottom of the page.

SSIS Multicast and Excel Destinations
Using SSIS Merge JOIN with CSV Files