Import Excel data into a database

Using Excel as a data source, importing data into a database, is a simple application of SSIS, is a sample Excel, and the data column is code and name

The first part, the data type in Excel is numeric type

1, create a package using SSDT, create Excel data source COMPONENT,SSDT to create an Excel connection in connection managers

Because the first row of the example Excel is a column name, you need to tick "names", and Excel Connection Manager is as follows

2, the data source component reads the data from Excel and passes it to other components, the data source component actually has the input and the output, the input refers to imports the Excel data into the data source component, the output refers to the data source component imports the Excel data to pass down.

Open the Advanced editor for the data source component by using show advanced Editor

In the input and Output properties tab, External columns is the input column for the Excel data Source component, and the output columns is the column for the Excel data source component. Each column has datatype and codepage.

By default, the Excel linker for SSIS treats numbers in Excel as numeric types

For external columns, you can modify datatype and codepage according to the actual needs, for numeric types, there is no need to focus on codepage, but for character types, codepage must match, otherwise the package will fail at run.

Because the values for both columns of the example Excel are numbers, the SSIS default setting is datatype to numeric types, and for datatype, although it can be modified, the data source component is not responsible for datatype conversions if external columns and output The datatype of columns is not the same, and the error is thrown when run. If you need convert DataType, you need to use the data Conversion component.

3, create a table in DB to receive Excel data Tbexcel, the data is similar to nvarchar, the data received is numeric, so that does not error.

CREATE table Dbo.tbexcel (code nvarchar, name nvarchar (10))

4, create an OLE DB data target component, open the advanced Editor, and see that there are two Destination Input to OLE:

External columns: Is the data column and its property information for the target table in db, this example refers to the columns and properties of the Tbexcel table

Input Columns: Is the data column and its property information passed by the upstream data source component

5, set the Mapping,execute package for the data source component and the data destination component, and successfully import the rows data

Part two, modifying the data type in Excel to a text type

6, if the data in Excel source is of the text type, you must consider codepage for implementation.

Modify sample Excel to modify the Name column to a text type

7, modify the target table in db to change the data column to varchar

If object_id (' Dbo.tbexcel ') is not nulldrop table dbo.tbexcelcreate table Dbo.tbexcel (code varchar (), name varchar (10))

8, during the Execute package, SSIS throws an error message, that is, the text in Excel uses a Unicode data type, and varchar is not Unicode, so you must convert to add data Converion Components for conversion

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

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 da TA 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, still error, error because the code page does not match, in the process of data transfer, code page 1252 can not be passed to the target table codepage 936

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

is OLE DB Destination Input, view the property of name in external columns, codepage is the property of the name in 936,input columns, codepage is 1252

Since the column copy of name is converted from data conversion, you only need to modify the codepage of the converted data column.

10, set the mapping of the column, execute the package again, all the way green, successfully import the rows

The third part, a little summary

• The text of Excel, the default data type is Unicode, and the length is 255
• varchar in db is not a Unicode type, nvarchar is a Unicode type
• If the codepage is inconsistent, it can be converted via the Data conversion component

Import Excel data into a database