SQL Server 2008 / 64Bit - How to create a query including Excel

Question

We are running SQL Server 2008 on 64bit boxes. We've had a request to create a query that has a Where clause that draws data from an Excel table. In pseudo code, the query might look as follows:

Select column1, column2, column3
From MyDatabase.MyTable
Where column4 IN (Select all values from column 'A' in SubIDs.xls whose sheet name is  'SubIDs')

Answer 1

It depends how often the data in the XLS changes: if it doesn't change often, then you can load it into a database table and query it directly. SSIS is a common way to automate this, but there's no 64-bit OLE DB provider for Jet, so you would need to use 32-bit packages:

http://msdn.microsoft.com/library/ms141766.aspx

Alternatively, you could create a linked server to the Excel spreadsheet and query it directly. See example E here (the same comment about the OLE DB provider applies):

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Personally I would load the data into MSSQL first if possible, it's just much easier.