SSIS和sql server 导入Excel数据到数据库中数据出现null

SSIS和sql server 导入Excel数据到数据库中数据出现null

客户提供的Excel如下所示。上方为表头，字符型，下方为对应数据。Number型。

Source：

QTR ~ 03/2007	QTR ~ 06/2007	QTR ~ 09/2007
ST-CNT.UNIT	ST-CNT.UNIT	ST-CNT.UNIT
465,124,985	490,627,220	502,894,146
34,685,645	39,212,669	42,611,936
14,976,035	15,944,699	16,605,866
6,641,850	7,173,852	7,292,634

但是通过SSIS的Excel组件，把源数据抽取进程序中是，在刚刚加载数据进来的预览阶段，便出现了number型数据为null的情况。预览如下：

QTR ~ 03/2007	QTR ~ 06/2007	QTR ~ 09/2007
ST-CNT.UNIT	ST-CNT.UNIT	ST-CNT.UNIT
null	null	null
null	null	null
null	null	null
null	null	null

发现Excel在导入数据的时候，会通过前面几行对整个的字段类型，对整个列有个基本的判断，所以默认下方数据都为字符而导致出错。

         同样问题依然会出现在，通过代码直接将Excel数据导入到sql server数据库中的情况。通过sql server导入Excel数据的代码如下。

 

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

 

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:/Users/mc/Desktop/CHPA 2007-2010 Quarter.xls;Extended Properties="EXCEL 8.0;HDR=YES "')...[Source$]

 

exec sp_configure 'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure 'show advanced options',0

reconfigure

 

在通过代码导入Excel数据时，需要注意的是要先配置sp_configure。否则直接执行代码会报错（SQL Server 阻止了对组件'Ad Hoc Distributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'Ad Hoc Distributed Queries'。有关启用'Ad Hoc Distributed Queries' 的详细信息，请参阅SQL Server 联机丛书中的"外围应用配置器"。

）。

 

解决方案：

在连接字符串中添加：

Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

“IMEX=1” 这个就是要添加的。