SSIS - Excel Querying

The power and flexibility of SSIS never ceases to amaze me. With just a few simple lines of code you can import and manipulate data straight out of excel.

This piece goes on to the actual excel connection provider.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\<FILENAME.xls>;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Instead of table loading the excel file on the datasource, utlize a SQL query such as this.

SELECT F1,F18 from [Sheet1$A3:D8]

WHERE 

  F1 IS NOT NULL

  ........