微软BI 之SSIS 系列 - 通过 OLE DB 连接访问 Excel 2013 以及对不同 Sheet 页的数据处理 - BIWORK

微软BI 之SSIS 系列 - 通过 OLE DB 连接访问 Excel 2013 以及对不同 Sheet 页的数据处理

2013-12-17 16:31 BIWORK 阅读(17038) 评论(22) 编辑收藏举报

文章更新历史

2014年9月7日 - 加入了部分更新内容，在文章最后提到了关于不同 Office Excel 版本间的连接问题。

开篇介绍

这篇文章主要总结在 SSIS 中访问和处理 Excel 数据的四个方面的主题内容 (都是处理以 .xlsx 结尾的 Excel 文件) -

如何在 SSIS 中集成对 Microsoft Excel 的访问支持以及注意事项。
如何在 SSIS 中连接和访问 Microsoft Excel 文件以及注意事项。
如何加载不同 Sheet 页的数据到同一个表中。
如何加载不同 Sheet 页的数据到不同的表中。

连接和处理 Microsoft Excel 文件的方式有很多种，包括使用 C#.NET 编程的形式加载处理数据，但本文只考虑在 SSIS 中如何加载 Microsoft Excel 文件中的数据。

在以前的 Microsoft Excel 文件版本中，使用 SQL Server 2008 R2 - BIDS 开发工具很容易处理，因为以前的版本是支持以 .xls 结尾的 Excel 文件，比如说 Excel 2003。Microsoft Office 版本升级之后，他们开始采用基于OpenXML的新的文件类型，也就是以 .xlsx 结尾的 Excel 文件类型。但是在 BIDS 中包括现在的 SQL Server 2012 - SSDT 版本的工具对以 .xlsx 结尾的 Excel 文件没有直接驱动支持，因此需要人工的去配置一下。

在 SSIS 中集成对 Microsoft Excel 的访问支持

在现有的 BIDS (SQL Server 2008 R2 及以前的版本中开发工具的简称) 和 SSDT (SQL Server 2012 的 BI 开发工具) 中打开一个 OLE DB Connection 看到的一些 OLE DB 的驱动。为什么不去直接使用 Excel Source 组件？因为 Excel Source 组件不支持以 .xlsx 后缀结尾的 Excel 连接和访问，因此需要使用变通的 OLE DB 访问方式。

需要去微软官方网站下载并安装驱动 - 点击这里，可以选择相应的语言，我选择的是英文版本。

一定要注意的是，在这里只能选择和安装32位的安装文件，因为我们的 BIDS 也好 SSDT 也好本身都是安装的都是32位的环境，所以如果安装了64位的驱动，BIDS 和 SSDT 也没有办法看到的。并且如果已经安装了 64 位的 Microsoft Access Data Engine 2010，那么就需要先卸载下来，否则 32位版本的驱动是安装不了的，低版本无法覆盖高版本驱动。

安装之后重启 SSDT 就能看到这个驱动了，我们就可以使用这个驱动连接我们的 Excel 了。

在 SSIS 中连接和访问 Microsoft Excel 文件

基于上面的驱动的安装，我们可以做一个简单的数据加载测试，同时里面也有一些注意事项是需要我们注意的。

测试目标表

USE BIWORK_SSIS
GO

IF OBJECT_ID('StaffExcel') IS NOT NULL
DROP TABLE StaffExcel 
GO

IF OBJECT_ID('DepartmentExcel') IS NOT NULL
DROP TABLE DepartmentExcel 
GO

CREATE TABLE DepartmentExcel
(
    ID INT,
    Department NVARCHAR(50),
    Manager NVARCHAR(50)
)

CREATE TABLE StaffExcel
(
    ID INT,
    FullName NVARCHAR(50),
    City NVARCHAR(50),
    Occupation NVARCHAR(50)
)

源数据是 Excel 的 Sheet 页中的数据，并且我们的 Excel Sheet 页上的数据格式应该是规范的，不规范的数据不适合通过这种方式处理。

我们要做的就是把 Sheet 页是 Department 的数据从 Excel 导入到 DepartmentExcel 表中，新建一个 SSIS Package 并新建一个 OLE DB Connection。选择 Microsoft Office 12.0 Access Database Engine OLE DB Provider，并指定 Excel 源文件的路径和名称。

注意在 All 页面中，一直要指定 Excel 12.0 否则是无法连接到 Excel 数据源的。

拖放一个 Dataflow Task 并创建一个 OLE DB Source 并直接使用刚才创建好的 OLE DB Connection，指定 Sheet 名称。

创建 OLE DB Connection 连接到目标表并创建 OLE DB Destination 连接到 OLE DB Source，这时会出现警告。因为从 Excel 中加载的数据，默认格式都变成了 255 长度的NVARCHAR 数据类型，这样会发生截断并且目标表类型也不匹配。

警告内容：Validation warning. DST_Department: {35C08A6A-4279-4153-8D20-4ED829E68EF9}: Truncation may occur due to inserting data from data flow column "Department" with a length of 255 to database column "Department" with a length of 50.

使用 Data Conversion 进行转换， Output Alias 是从 Data Conversion 向下输出的转换后的别名。

转换完了然后配置后面的 Data Mapping，注意 Data Mapping 中要 Map 的是 Output Alias 的那些 Column 而不是 Input Column 中的那些列。执行 SSIS Package 的时候出现错误，可以从下图中看出来有以下两种错误信息。

第一种说明是连接失败，发生在 Task DST_Department 中：

[OLE_DB_SRC_Department [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "E:\WorkSpace\Input\ExcelSource.xlsx" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

第二种说明是数据源连接有问题，并且指明了错误的原因：因为我们的驱动是 32 位模式，但是这个包仍然是默认64位模式运行，因此在 64位运行环境下是无法检测到这种只支持32位链接的驱动的。

[Connection manager "E:\WorkSpace\Input\ExcelSource.xlsx"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

在 SSIS 项目，右键属性来修改包的运行模式改成 False 让它在32位模式运行。

保存并运行成功，有4条数据顺利加载到数据表中。

查询 DepartmentExcel 表的结果。

如何加载不同 Sheet 页的数据到同一个表中

这里不同的 Sheet 页是指在同一个 Excel Source 文件中有很多的 Sheet 页，但是这些 Sheet 页中的数据格式都是一样的，如下图所示。

这个 Excel 文件有三个 Sheet 页，一个是 Department，另外一个是我在博客园的ID - BIWORK，另外一个随便就叫 Sheet1 了。这种场景常常出现，比如有可能按天保存数据到同一个 Excel 文件中，每一个 Sheet 页上的数据格式是一致的，那么就可以用我们这里的解决方案来完成了。

BIWORK 页中的数据格式和 Department 中是一样的，也就是 Column 列格式是一样的， Sheet1 中也是如此。

这里可以考虑使用循环，思路是循环同一个 Excel 源上的 Sheet 页，每次在循环中替换 Sheet 名就可以了。

新建一个字符串类型的变量，变量的名称一定要是 Sheet Name，可以是任意中的一个比如 Department 或者 BIWORK，但一定要有，后缀加上 $。

新建一个 OLE DB Connection，操作方法和上面的示例一样的，别忘记修改 All 里面的 Excel 12.0。但是一定要注意，在我们这个例子有，是有两个连接到同一个 Excel 源的 OLE DB 连接的。一个是下面用来循环遍历 Sheet Name 的，一个是用来加载数据的。

添加一个 Foreach Loop 容器，按照下图显示操作和配置，这里的循环作用是循环指定 EXCEL 源上的 Table Name 即 Sheet Name。这个链接源就是上面一个步骤创建好的，再次强调 - 它只用来遍历 Excel Sheet Name，不会和数据有任何交互！

在变量配置中，指定 SheetName 变量来接受从 Excel 源中遍历来的 SheetName。特别要说明的是，这里的 Index 只能写成 2，表示 SheetName。它并不表示 Sheet 页的数量，这一点一定要注意，尝试使用 Index 0 或者 1 是取不到任何有意义的东西的。

把示例一种的 Data Flow Task 直接拷贝放入这个容器中，然后修改一下 OLE DB Source ，因为此时的表名是由变量来决定的了。

保存并运行，要注意到 ExcelSourceSame 和中间的那个连接管理器其实都是连接到同一个数据源，但是它们的作用不同。

刚才新添加了几个 Sheet，名字随便取。

执行完上面的包之后查询一下数据库，不同 Sheet 页中的数据全部加载到同一张表了。

如何加载不同 Sheet 页的数据到不同的表中

所谓不同的 Sheet 页是指在同一个 Excel 数据源中的 Sheet 页中的数据格式不同，要把它们分别加载到不同的表中。

基于上面的示例 Excel，我加上一个 Staff 页数据，这个页的数据目标表是文章开头处创建的 StaffExcel 表。

所以在这个示例中，Excel 源中的 Sheet 页与数据库中的表就要求分开被处理了。

Sheet - Department, BIWORK, Sheet1, Sheet2, Sheet3 都应该指向 DepartmentExcel。

Sheet - Staff 指向 StaffExcel。

要解决这个问题首先要明白在建立从数据源到目的表的过程中，需要提前建立好数据源列到目标表列的 Column Mapping。因此不能使用 Department Sheet 到 DepartmentExcel 表的 Data Mapping 来代替 Staff Sheet 到 StaffExcel 表的 Data Mapping，所以动态根据表名直接连接到目的表而想绕过 Data Mapping 这一点很难做到。

那这里有一种变通的方法，就是先为源与目标表建立好 Data Mapping，然后根据对 Sheet Name 的判断自动将 Sheet 分发到不同的 Data Mapping。

对上面的例子做出如下修改：

1. 添加一个 Mapping 表，用来将 Sheet Name 进行归类，这种是需要提前收集的。

在一个自动化的操作流程中，这个数据是可以实现自动添加的。因为首先 Excel 源本身也有可能就是通过程序输出的，因此在输出的过程中是可以对 SheetName 的命名做出要求的，包括一个 Excel 上的 Sheet 表 Mapping 分类。那么这种相对规范的 Excel 表是完全有可能适用于我们这个示例提出的解决方案的，除此之外一切不规范的 Excel 建议通过 C# Script 解决。

IF OBJECT_ID('TableSheetMapping') IS NOT NULL
DROP TABLE TableSheetMapping 
GO

CREATE TABLE TableSheetMapping
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DestTableName NVARCHAR(50),
    SheetName NVARCHAR(50)
)

INSERT INTO TableSheetMapping VALUES
('DepartmentExcel','Department'),
('DepartmentExcel','BIWORK'),
('DepartmentExcel','Sheet1'),
('DepartmentExcel','Sheet2'),
('DepartmentExcel','Sheet3'), 
('StaffExcel','Department')

2. 添加变量。

SheetName - 是从 Foreach Loop 循环得到的 Sheet 页的名称，但是只仅仅通过 N 多的 Sheet 页是不知道要匹配对应的表的。
TableName - 因此这里的 TableName 就是根据 Sheet 页的名称到数据库中去查来的一个结果。
DepartmentSheet - 用来指定与 DepartmentExcel 表建立连接的 SheetName。
StaffSheet - 同 DepartmentSheet。

因此只有添加了 Department$ 和 Staff$，OLE DB Source 才知道数据源是什么样的数据结构，该如何与下游目标表与匹配。

3. 添加一个 Staff Data Flow Task，并且修改相应的配置。

注意 Department Data Flow Task 也需要修改要将 SheetName 换成 DepartmentSheet。

下面是新添加的 DST_Staff_Sheet 控制流，命名应该叫 DFT，是我写错了就不改了。

DST_Staff_Sheet 中三个组件都需要修改。

OLE_DB_SRC_Staff 指定表名变量，其它配置省略。

那么现在要做的事情就是分发 Sheet 了，在 Foreach Loop 中添加一个 Execute SQL Task 用来根据 SheetName 获取 Table 名称。

参数变量 SheetName 是从 Loop 循环得到的。

查询的结果保存在 TableName 变量中。

新添加一个 Script Task 并且传入变量。

Script Task 中的逻辑主要是用来接收 TableName，根据TableName判断来到底是 DepartmentSheet 变量还是 StaffSheet 变量来接受循环中产出的 Sheet 名称，这样下游的 Data Flow Task 就可以根据 SheetName$ 来获取数据源的数据通道了。

public void Main()
        {
            // TODO: Add your code here
            string tableName = Dts.Variables["User::TableName"].Value.ToString();
            string sheetName = Dts.Variables["User::SheetName"].Value.ToString();

            if (tableName == "DepartmentExcel")
                Dts.Variables["User::DepartmentSheet"].Value = sheetName;
            
            if (tableName == "StaffExcel")
                Dts.Variables["User::StaffSheet"].Value = sheetName;

            Dts.TaskResult = (int)ScriptResults.Success;
        }

最后要做的一件事情就是使用条件表达式决定流程的走向，根据表名来判断 Script Task 的输出。

保存并执行 SSIS Package，所有的数据都分散到不同的执行流程中了。

查询一下数据库，数据全部进去了并且 Department 和 Staff 是从不同的 Sheet 页上取到的数据。

问题

为什么看到 TableName 在逻辑判断的过程中出现了好几次？

我们假设是一个 Excel 文件中 Sheet 页比较多的情况，因此使用了一个数据库 Mapping 表，根据 Mapping 表来看一次循环中当前的 Sheet 页是属于哪一个目标表的数据源。

根据 SheetName 取到 Table Name 之后，需要在 C# Script 中使用 Table Name 来决定在下面的两个 Data Flow Task 中表变量 DepartmentSheet 和 StaffSheet 到底是哪一个源 Sheet。

确定好了源 Sheet 后，再通过 Table Name 作为条件约束控制流程的分支走向，这样就避免了多次循环同一 Sheet 的问题。

总结

其实在 SSIS 中处理 Excel 包括连我自己都不太喜欢这种方式，因为经常会碰到连接的问题，字符串格式转换的问题，不规范的数据格式问题等等。越是不规范的 Excel 文件越是应该在源头控制它，比如在 Excel 文件输出的时候通常会告诉上游产出者我们对 Excel 文件格式的要求。不要求做到绝对规范，但是最起码会做到一个 Excel 文件只保留一个 Sheet，一个 Excel 只表示一个数据源。这样在下游 SSIS 处理中就会减少很多的工作量，因此在数据整理收集阶段，沟通的工作是非常非常重要的，上游数据源一个很小的改动或者格式调整对下游数据处理就会产生极大的或好或坏的影响。

2014年9月7日后记更新

非常奇怪的一件事情发生了！在我今天的一次测试中，我发现只要安装完了 AccessDatabaseEngine 之后，我完全就可以直接使用 Excel Source 和 Excel Destination 就可以操作 Excel 数据的抽取和导出了！并且在 64 位下也是可以正常运行的，不需要调整成 32 位的模式。

直接使用 Microsoft Excel 2007 也是可以的。

在检查它的 Provider 时，发现它本质上仍然是 -

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\BIWORKSPACE_FILE\TS_BIWORK_SSIS\INPUT_DIRECTORY\012\Hotel.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

我能想起来的区别就是：

本文中测试的环境安装的 Office 版本是 Office 365，而现在新的测试环境 2014年9月7日 - 是 Microsoft Office Professional Plus 2013。

现在唯一的问题是，由于我在写这篇文章时的测试环境已经丢失，我现在安装的都是 Professional Plus 2013，所以无法还原在写这篇文章时的配置，包括报错信息的出现以及问题解决前后的失败与成功对比。所以希望大家在看到这篇文章的时候，如果有可能也帮助验证一下在自己当前版本下关于这个 Excel 的连接情况。

但无论如何，多了一种选择，多了一种解决方案，希望能够帮助到大家。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。

刷新页面返回顶部

BIWORK 人生如棋，我愿为卒，行动虽缓，可谁曾见我后退过！