Excel 源作为查找转换连接 [英] Excel Source as Lookup Transformation Connection

问题描述

我需要查找转换任务，其中 Connection 将是 Excel 源.但 SSIS 只允许将 OLEDB 连接设置为连接.

I need to have Lookup Transformation Task, where Connection will be a Excel Source. But SSIS allow to set only OLEDB Connection as Connection.

是否有可能使用 Excel 文件作为查找转换的连接?

Is there any possibility to use Excel file as Connection for Lookup Transformation?

推荐答案

合并排序是一个选项，但如果您真的想使用 Excel 作为 SSIS 查找任务的源，您可以这样做.

Merge sort is an option, but if you really want to use Excel as a source for the SSIS Lookup task, you can do it.

如您所见，您不能在查找任务中使用 Excel 连接管理器，它只接受 OLE DB 连接管理器.诀窍是将 OLE DB 连接管理器与 Excel 电子表格一起使用.

As you have correctly discerned, you cannot use the Excel Connection Manager in a Lookup task, it only accepts the OLE DB connection manager. The trick then, is to use an OLE DB connection manager with an Excel spreadsheet.

在这个例子中，我有一个包含州代码及其全名的电子表格，而我的源数据只有缩写.本演练将针对 Excel 连接查找任务以检索这些值.

For this example, I have a spreadsheet with state codes and their full name and my source data only has the abbreviations flowing through. This walk through will wire up a lookup task against Excel to retrieve those values.

请记住这些警告:查找区分大小写，无论源数据库(或本例中的文件)是否区分大小写.Excel 字符串总是会被解释为 unicode/nvarchar.

Keep these caveats in mind: Lookups are case sensitive, regardless of whether the source database (or file in this case) is case sensitive. Excel strings are always going to be interpreted as unicode/nvarchar.

给定一个位于 C:\tmp\LookupReference.xlsx 的简单文件，如下所示

Given a simple file sitting at C:\tmp\LookupReference.xlsx that looks like the following

首先要建立一个OLE DB连接管理器.将默认的Native OLE DB\SQL Server Native Client 10.0"改为Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider".在服务器或文件名中，找到源文件.此时点击Test Connection会报错.

We must first establish an OLE DB Connection Manager. Instead of the default "Native OLE DB\SQL Server Native Client 10.0" change that to "Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider". In the Server or file name, locate the source file. Clicking Test Connection at this point will result in an error.

魔法"来了.单击全部选项卡并滚动到顶部.在扩展属性中，添加指示的字符串Excel 12.0;HDR=YES;IMEX=1;"这告诉提供程序我们将使用带有标题行的 Excel 12.0(.xlsx 格式)，并且 IMEX 1 告诉驱动程序将存在混合数据.

Here comes the "magic." Click the All tab and scroll to the top. In Extended Properties, add the indicated string "Excel 12.0;HDR=YES; IMEX=1;" This tells the provider that we are going to use Excel 12.0 (.xlsx format) with a header row and the IMEX 1 tells the driver there will be intermixed data.

您的包裹现在看起来像这样.具有扩展属性集并假定预先存在的数据流的连接管理器

Your package sould now look something like this. A connection manager with extended properties set and assumes a preexisting data flow

为了简化问题，我有一个脚本源，它生成带有状态代码 MO、KS 和 NE 的 3 行数据，并将它们发送到管道中.您的来源显然会有所不同，但概念将保持不变.注释中提供的示例代码.

To simplify matters, I have a script source that generates 3 rows of data with state codes MO, KS and NE and sends them down the pipeline. Your source will obviously be different but the concept will remain the same. Sample code provided in the annotation.

在查找转换中，您需要针对电子表格编写查询.它类似于普通的数据库查询，除了你的表将是 Sheet1$ 除非你有一个命名范围，你的表将是 MyRange 注意引用时需要 $一张.使用上面的示例电子表格，我的查询将是

In your lookup transformation, you will need to write a query against the spreadsheet. It's similar to a normal database query except your table is going to be Sheet1$ unless you have a named range in which your table would be MyRange Note the $ is required when referencing a sheet. Using the sample spreadsheet above, my query would be

SELECT
    S.StateCode
,   S.StateName
FROM 
    `Sheet1$` S

我将我的脚本任务列 StateCode 映射到参考查询的 StateCode 列并检查 StateName 字段，因为我想将其添加到我的数据流中.

I map my Script task column StateCode to the reference query's StateCode column and check the StateName field as I want to add that to my data flow.

我在查找任务之后放置了一个数据查看器来验证我的查找是否有效

I've put a Data viewer after the Lookup task to verify my lookups worked

一切正常，我们都很开心.

Everything works and we're all happy.

如果您使用的是 .xls 文件，则需要进行以下更改.- 在您的连接管理器中，而不是 Office 12 提供程序，选择本机 OLE DB\Microsoft Jet 4.0 OLE DB 提供程序"- 扩展属性变为EXCEL 8.0;HDR=Yes; IMEX=1;"

If you are using a .xls file, you need to make the following changes. - In your Connection Manager, instead of the Office 12 provider, select the "Native OLE DB\Microsoft Jet 4.0 OLE DB Provider" - The Extended Properties become "EXCEL 8.0;HDR=Yes; IMEX=1;"

在 在 SSIS 查找转换中使用 Excel.您还可以使用缓存连接管理器在 SSIS 2008+ 中使用任何源进行查找

Blogged this at Using Excel in an SSIS lookup transformation. You can also use a Cached Connection Manager to use any source for lookups in SSIS 2008+

这篇关于Excel 源作为查找转换连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！