How to do an INNER JOIN in SSIS Excel Source compo

Question

SELECT   [Sheet1$].ID,
CLng([Sheet1$].RecordID) AS RecordID, 
[Sheet1$].col1, 
[Sheet1$].col2, 
[Sheet1$].col3, 
[Sheet1$].col4, 
[Sheet1$].col5,
[Sheet2$].Name
FROM      [Sheet1$]
INNER JOIN [Sheet2$] ON 
[Sheet1$].RecordID = [Sheet2$].RecordID

I have the above sample SQL Command in an SSIS Excel Source component. As seen in that query i'm doing an inner join on two excel sheets (Sheet1 and Sheet2) in the same workbook.

At this point the query executes well with out any errors.

However, i am not able to join on a 3rd sheet (Sheet3). When i try to inner join on sheet3, i get the following error message.

An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80040E14 Description: "Syntax error (missing operator) in query expression '[Sheet1$].RecordID = [Sheet2$].ReportID INNER JOIN [Sheet3$] ON [Sheet1$].RecordID = [Sheet3$].RecordID'

So i am basically unable to do an inner join on two or more excel sheets. I'm only able to inner join on one excel sheet. The syntax i am using works in SQL Server, so i am wondering if its supposed to work in a SSIS Excel source SQL Command as well since it seems to be using the Microsoft Access Database Engine.

Below is the query with the second join that is generating the above error:

 SELECT   [Sheet1$].ID,
    CLng([Shee1$].RecordID) AS RecordID, 
    [Sheet1$].col1, 
    [Sheet1$].col2, 
    [Sheet1$].col3, 
    [Sheet1$].col4, 
    [Sheet1$].col5,
    [Sheet2$].Name
    FROM      [Sheet1$]
    INNER JOIN [Sheet2$] ON 
    [Sheet1$].RecordID = [Sheet2$].RecordID
    INNER JOIN [Sheet3$] ON 
    [Sheet1$].RecordID = [Sheet3$].RecordID

Answer 1

Lets try to cheat:

SELECT  
    CLng(x.RecordID) AS RecordID, 
    x.col1, 
    x.col2, 
    x.col3, 
    x.col4, 
    x.col5,
    x.Name
    FROM (
    SELECT
    [Sheet1$].RecordID, 
    [Sheet1$].col1, 
    [Sheet1$].col2, 
    [Sheet1$].col3, 
    [Sheet1$].col4, 
    [Sheet1$].col5,
    [Sheet2$].Name    
    FROM [Sheet1$]
    INNER JOIN [Sheet2$] ON 
    [Sheet1$].RecordID = [Sheet2$].RecordID
) as x
    INNER JOIN [Sheet3$] ON 
    x.RecordID = [Sheet3$].RecordID

Answer 2

Ok, i was doing it the wrong way. Microsoft access database engine used by the SSIS Excel Source component handles joins differently than SQL Server.

Apparently, you need to have n - 2 left parentheses after the from clause and one right parenthesis before the start of each new join clause except for the first, where n is the number of tables being joined together.

The reason is that Access's join syntax supports joining only two tables at a time, so if you need to join more than two you need to enclose the extra ones in parentheses.

Quoted from Access-SQL: Inner Join with multiple tables

And confirmed at http://office.microsoft.com/en-001/access-help/inner-join-operation-HA001231487.aspx

So the below query now works

SELECT   [Sheet1$].ID,
CLng([Shee1$].RecordID) AS RecordID, 
[Sheet1$].col1, 
[Sheet1$].col2, 
[Sheet1$].col3, 
[Sheet1$].col4, 
[Sheet1$].col5,
[Sheet2$].Name
FROM      (([Sheet1$])
INNER JOIN [Sheet2$] ON [Sheet1$].RecordID = [Sheet2$].RecordID)
INNER JOIN [Sheet3$] ON [Sheet1$].RecordID = [Sheet3$].RecordID