导入excel数据到指定数据库-sqlserver导入excel

admin

作者

将Excel作为数据源，将数据导入数据库，是SSIS导入excel数据到指定数据库的一个简单导入excel数据到指定数据库的应用，下图是示例Excel，数据列是code和name

第一部分，Excel中的数据类型是数值类型

1，使用SSDT创建一个package，创建Excel data source component，SSDT会在Connection Managers中创建一个Excel的connection

由于示例Excel的首行是列名，所以需要勾选"First row has column names"，Excel connection manager 如下

2，数据源组件将Excel中的数据读取出来，并传递给其他组件，数据源组件其实是有输入和输出的，输入是指将Excel的数据导入到数据源组件中，输出是指将数据源组件中导入的Excel数据向下传递。

打开数据源组件的Advanced editor，通过Show Advanced Editor来打开

在Input and Output Properties选项卡中，External columns是Excel数据源组件的输入列，Output Columns是Excel数据源组件的输出列，每一列都是有DataType和CodePage。

默认情况下，SSIS的Excel连接器将Excel中的数字作为数值类型来对待

对于External columns，可以根据实际需要修改DataType和CodePage，对于数值类型，不需要关注CodePage，但是对于字符类型，CodePage就必须匹配，否则package在run时就会fail。

由于示例Excel的两列的值都是数字，SSIS默认设置DataType为数值类型，对于DataType，虽然可以修改，但是数据源组件并不负责DataType的转换，如果External columns 和Output Columns的DataType不相同，run的时候会抛出error。如果需要convert DataType，需要使用Data Conversion 组件。

3，在db中创建接收Excel数据的表tbExcel，数据类似是Nvarchar，接收的数据是数值型，这样并不会报错。

create table dbo.tbExcel(code nvarchar(10),name nvarchar(10))

4，创建一个Oledb数据目标组件，打开Advanced Editor，看到Ole db Destination Input 也有两个:

External columns导入excel数据到指定数据库：是DB中的目标表的数据列及其属性信息，本例是指 tbExcel 表的列和属性

Input Columns：是上游数据源组件传递的数据列及其属性信息

5，设置数据源组件和数据目标组件的列的mapping，execute package，成功导入13 rows 数据

第二部分，将Excel中的数据类型修改为文本类型

6，如果Excel source 中的数据是文本类型，实现起来必须考虑CodePage。

修改示例Excel，将name列修改为文本类型

7，将Db中的目标表做修改，将数据列修改为varchar

if object_id('dbo.tbExcel') is not nulldrop table dbo.tbExcelcreate table dbo.tbExcel(code varchar(10),name varchar(10))

8，在execute package的过程中，ssis抛出错误信息，也就是说Excel中的文本使用的数据类型是unicode 的，而varchar并不是unicode，所以必须进行转换，在package中加入Data converion组件进行转换

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [OLE DB Destination [166]]: Column "name" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SSIS.Pipeline]: "OLE DB Destination" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

9，execute package，仍然出错，错误原因是Code Page 不匹配，在数据传递的过程中，不能将code page为1252的数据传递到codepage为936的目标表中

Error at Data Flow Task [OLE DB Destination [203]]: The column "Copy of name" cannot be processed because more than one code page (1252 and 936) are specified for it.

下图是Ole db Destination Input，在External columns中查看name的属性，codepage是936，Input columns中查看name的属性，codepage是1252