如何在SSIS Excel Source组件中进行INNER JOIN [英] How to do an INNER JOIN in SSIS Excel Source component

问题描述

  SELECT [Sheet1 $]。ID，
 CLng（[Sheet1 $]。RecordID）AS RecordID，
 [Sheet1 $]。col1，
 [Sheet1 $]。col2，
 [Sheet1 $]。col3，
 [Sheet1 $]。col4，
 [Sheet1 $]。col5，
 [Sheet2 $]。名称
 FROM [Sheet1 $] 
 INNER JOIN [Sheet2 $] ON 
 [Sheet1 $]。RecordID = [Sheet2 $]。RecordID 
  / pre> 
 
 
我在SSIS Excel Source组件中有上面的示例SQL命令。正如在该查询中所看到的，我在同一个工作簿中的两个excel表（Sheet1和Sheet2）上进行内部连接。
 
 
 
此时查询执行得很好任何错误。
 
 
 
但是，我无法加入第3张表（Sheet3）。当我尝试内部加入sheet3时，我收到以下错误消息。 
 
 
 OLE DB记录可用。来源：Microsoft Access数据库引擎Hresult：0x80040E14说明：查询表达式[Sheet1 $]中的语法错误（缺少运算符）RecordID = [Sheet2 $]。ReportID 
 INNER JOIN [Sheet3 $] ON 
 [Sheet1 $]。RecordID = [Sheet3 $]。RecordID'
 
 
所以我基本上无法做内部连接两个或更多的excel表，我只能内部加入一个excel表。我使用的语法在SQL Server中工作，所以我想知道
如果它应该在SSIS Excel源SQL命令中工作好吧，因为它似乎正在使用Microsoft Access数据库引擎。
 
 
 
以下是第二个连接生成上述错误的查询：
  SELECT [Sheet1 $]。ID，
 CLng（[Shee1 $]。RecordID）AS RecordID，
 [Sheet1 $]。 col1，
 [Sheet1 $]。col2，
 [Sheet1 $]。col3，
 [Sheet1 $]。col4，
 [Sheet1 $]。col5，
 [Sheet2 $]。名称
 FROM [Sheet1 $] 
 I NNER JOIN [Sheet2 $] ON 
 [Sheet1 $]。RecordID = [Sheet2 $]。RecordID 
 INNER JOIN [Sheet3 $] ON 
 [Sheet1 $]。RecordID = [Sheet3 $] .RecordID 
  
 
 
解决方案
好的，我做错了。 SSIS Excel Source组件使用的Microsoft Access数据库引擎处理与SQL Server不同的连接。 
 
 
显然，在
子句后面需要有n  -  2个左括号，在每个开始之前需要一个右括号new join 
子句除了第一个，其中n是
连接在一起的表数。
 
 
 
原因是Access的连接语法支持一次只加入两个
表，所以如果你需要加入两个以上，你需要
在圆括号里加上额外的。
 
 
引用自 Access-SQL：具有多个表的内部连接 
 
 
 
并在 http://office.microsoft.com/en-001/access-help/inner-join-operation-HA001231487.aspx  
 
 
 
所以下面的查询现在工作
  SELECT [Sheet1 $]。ID，
 CLng（[Shee1 $]。RecordID）AS RecordID，
 [Sheet1 $]。col1，
 [Sheet1 $]。col2，
 [ Sheet1 $]。col3，
 [Sheet1 $]。col4，
 [Sheet1 $]。col5，
 [Sheet2 $]。名称
 FROM（（[Sheet1 $]） 
 INNER JOIN [Sheet2 $] ON [Sheet1 $]。RecordID = [Sheet2 $]。RecordID）
 INNER JOIN [Sheet3 $] ON [Sheet1 $]。RecordID = [Sheet3 $]。RecordID 
  
 
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

解决方案
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


                        
这篇关于如何在SSIS Excel Source组件中进行INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！