阻止SSIS import excel时的默认行为

为什么SSIS总是错误地获取Excel数据类型，以及如何解决它！

由Concentra发布 2013年5月15日

分享此页面

分享

 

发现Concentra的分析解决方案

 

Concentra的分析和商业智能团队将信息转化为洞察力，从而为您提供数据优势。学到更多。

 

分析解决方案

作为具有Microsoft工具堆栈的BI开发人员，我经常处理多个数据源，其中一个是Excel，这是Data Warehousing项目中非常常见的数据源。正如你们中的一些人可能知道的那样，在SSIS（SQL Server Integration Services）中使用Excel会遇到问题，如果你知道这些，这篇文章将更详细地帮助人们并为你提供一些可能的解决方案来帮助你一段时间

此博客还将帮助回答有关以下问题：

• 使用Excel作为源时，为什么SSIS没有正确获取我的数据类型
• 当excel文件包含数据时，为什么我的某些列为NULL
• 如何解决我的Excel源代码问题
• Excel SSIS驱动程序问题

我们在使用SSIS和Excel时遇到的问题的主要驱动因素源于SSIS自动确定连接到Excel源时的数据类型。SSIS Excel驱动程序根据读取前8行的值确定每列的数据类型。

• 如果前8个记录包含相同数量的数字和字符类型 - 则优先级为数字（图1和2中的 column2 ）
• 如果大多数前8个记录都是数字，那么它将数据类型指定为数字，所有字符值都读为NULL （图1和2中的 column3 ） 
• 如果大多数前8个记录都是字符类型，那么它将数据类型指定为字符串，并将所有数值读取为NULL （图1和2中的 column4 ）

 

图1：Excel输入数据

 

 

图2：SSIS Excel源组件元数据

 

 

图3：SSIS Excel源组件中的“输出预览”窗口

 

从上面的屏幕截图中可以看出，我们在目标表中收到NULL，我们有数据要加载，这不是所需的输出。那么，我们该如何解决这个问题呢？

通过将IMEX = 1（导入导出模式）属性添加到连接字符串，excel正确读取数据（参见图4）。

设置连接字符串属性如下：

Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C：Folder1Book1.xls; Extended Properties =“EXCEL 8.0; HDR = YES; IMEX = 1”;

 

图4：SSIS Excel连接管理器连接字符串属性

 

添加此属性后，excel驱动程序将具有混合类型的列读取为Unicode String数据类型（请参见图5）并正确读取数据（请参见图6）。

 

图5：SSIS Excel源组件MetadatA

 

 

 

图6：SSIS Excel源组件中的输出预览窗口，IMEX = 1

 

此外，在excel文件中前8行为NULL的情况下，我们需要编辑注册表设置并将TypeGuessRows属性设置为0，以便让excel驱动程序使用文件中的所有行来正确地确定数据类型（请参阅图7）通过对所有行而不是第1行进行采样。此属性允许的值范围为0-16。因此，我们可以将1-16行或所有行作为允许的样本大小。

需要更改的密钥的位置如下：

HKEY_LOCAL_MACHINE - > SOFTWARE - > Wow6432Node - > Microsoft - > Jet - > 4.0 - > Engines - > Excel - > TypeGuessRows的值为0.（默认情况下包含8）

图7：注册表编辑器 - Excel驱动程序TypeGuessRows属性