ssis 导入excel_使用SSIS包将MS Excel数据导入数据库
ssis 导入excel
The easiest way to import the data into the SQL database is using the Import Data wizard from SQL Server Management Studio. Following the steps through the wizard, user can choose the source file, and the destination table to import the data. However, importing the data this way narrows the process and won’t allow user to execute more complex tasks with different kind of the data, and multiple files. The SSIS packages offer solution for this kind of problem. The SSIS package represents tool for the ETL (Extract-Transform-Load) processing, and it can be used not just to import the data into the database, but to transform, filter, group the data and many other tasks. This article will give explanation how to import the simple Excel worksheet into the SQL database
将数据导入SQL数据库的最简单方法是使用SQL Server Management Studio中的“ 导入数据”向导。 按照向导中的步骤操作，用户可以选择源文件和目标表以导入数据。 但是，以这种方式导入数据会缩小流程，并且不允许用户使用不同种类的数据和多个文件执行更复杂的任务。 SSIS软件包为此类问题提供了解决方案。 SSIS包代表用于ETL （提取-转换-加载）处理的工具，它不仅可以用于将数据导入数据库，还可以用于转换，过滤，分组数据以及许多其他任务。 本文将说明如何将简单的Excel工作表导入SQL数据库
Before creating an SSIS package, the user needs to create new project in BIDS by choosing the Integration Services from the Business Intelligence list from the left, navigate to the Integration Services project , and define name for the project like shown in the image below
在创建SSIS包之前，用户需要通过从左侧的Business Intelligence列表中选择Integration Services ，在BIDS中创建新项目，导航至Integration Services项目 ，然后为项目定义名称，如下图所示。
从MS Excel工作表导入 (Importing from an MS Excel worksheet)
To be able to import the data from the Excel worksheet into the SQL database, the user first needs to define the Data Flow Task, dragging from the SSIS Toolbox into the Control Flow area like shown in the image below
为了能够将数据从Excel工作表导入SQL数据库，用户首先需要定义数据流任务 ，将其从SSIS工具箱拖到“ 控制流”区域，如下图所示。
Double-clicking the Data Flow Task will open the Data Flow tab. Before selecting and configuring the source and the destination, connections for both of them must be created. Since the Excel worksheet is the source, the Excel Connection will be created, and for the destination, the OLE DB connection. Both of these connections can be created by right click on the Connection Manager in the Solution Explorer, and choosing the New Connection Manager from the drop down list, like shown in the image below
双击“ 数据流任务”将打开“ 数据流”选项卡 。 在选择和配置源和目标之前，必须创建它们的连接。 由于Excel工作表是源，因此将创建Excel Connection ，对于目标，将创建OLE DB连接 。 可以通过在Solution Explorer中的Connection Manager上单击鼠标右键，然后从下拉列表中选择New Connection Manager来创建这两个连接，如下图所示。
From the list of the connections in the New Connection Manager dialog, the Excel connection will be used to connect the external Excel worksheet with the project. Clicking the Browse button, Excel file can be connected with the task like shown in the image below
在“ 新建连接管理器”对话框的连接列表中，将使用Excel连接将外部Excel工作表与项目连接。 单击浏览按钮，Excel文件可以与下图所示的任务连接
Note: The external Excel worksheet must have the “xls” extension.
注意：外部Excel工作表必须具有“ xls”扩展名。
Connection with the destination, in this case SQL Server database, can be established through the OLE DB connection, choosing it from the list in the New Connection Manager window.
可以通过OLE DB连接建立与目标的连接，在本例中为SQL Server数据库，可以从“ 新建连接管理器”窗口的列表中选择它。
In the Connection Manager window, Native OLE DB\SQL Server Native Client 11.0 must be selected from the Provider drop down list. In the Server Name filed, if the server is local, it can be defined by entering “.” (without quotes). Otherwise, proper server name must be typed
在“ 连接管理器”窗口中，必须从“ 提供程序”下拉列表中选择“本机OLE DB \ SQL Server本机客户端11.0” 。 在服务器名称字段中 ，如果服务器是本地服务器，则可以通过输入“。”进行定义。 （不带引号）。 否则，必须输入正确的服务器名称
If the server name is correct, the user will be able to choose one of the databases from the server, where the data from the Excel worksheet will be imported, like shown in the image below
如果服务器名称正确，则用户将能够从服务器中选择一个数据库，Excel工作表中的数据将导入其中，如下图所示。
When the connections with the source and the destination is created, the Data Flow Tasks can be defined and configured in the Data Flow window. From the SSIS Toolbox, the Excel Source, and the OLE DB Destination tasks must be imported into the Data flow window, as shown in the image below
创建与源和目标的连接后，可以在“ 数据流”窗口中定义和配置“ 数据流任务” 。 必须从SSIS工具箱 ， Excel Source和OLE DB Destination任务导入“ 数据流”窗口，如下图所示。
Double click on the Excel Source task will open the Excel Source Editor. Selecting the Excel connection from the Connection manager list allows user to choose the Name of the Excel sheet from the list, like shown in the image below
双击Excel Source任务将打开Excel Source Editor 。 从“ 连接管理器”列表中选择Excel连接，允许用户从列表中选择Excel工作表的名称，如下图所示。
In the Column tab, the user can choose what columns from the excel file will be imported by checking the boxes for desired columns, and rename output columns, as shown in the image below
在“ 列”选项卡中 ，用户可以通过选中所需列的框来选择要从excel文件中导入哪些列，然后重命名输出列，如下图所示。
To configure the OLE DB Destination it must be connected to the Excel Source first. Double click on the OLE DB Destination will open the editor shown below
要配置OLE DB目标 ，必须首先将其连接到Excel Source 。 双击OLE DB Destination将打开如下所示的编辑器
In the OLE DB connection manager list, previously defined connection must be selected, in this case, connection is established with the AdventureWorks2012 database on the local server. From the table list, desired destination table can be chose, or new table can be created clicking the New button and entering the SQL syntax
在OLE DB连接管理器列表中，必须选择先前定义的连接，在这种情况下，将与本地服务器上的AdventureWorks2012数据库建立连接。 从表列表中，可以选择所需的目标表，或者可以单击“ 新建”按钮并输入SQL语法来创建新表。
After the destination database table is selected, the columns must be mapped before importing the data. That could be done from the Mappings tab like shown in the image below
选择目标数据库表后，必须在导入数据之前对这些列进行映射。 可以从“ 映射”选项卡完成，如下图所示
Not all the columns from the source need to be imported. Mapping is a way of filtering the input data, and pointing to the specific columns in the database table, where the column from the source will be imported. Choosing the <ignore> from the input column list will exclude it from importing
并非源中的所有列都需要导入。 映射是一种过滤输入数据并指向数据库表中特定列的方法，将从源中导入该列。 从输入列列表中选择< ignore >会将其从导入中排除
After the columns are mapped, the SSIS package can be executed by right click on it from the Solution Explorer, and choosing the Execute Package option from the drop down list like shown in the image below
映射完列后，可以通过在解决方案资源管理器中右键单击SSIS包并从下拉列表中选择“ 执行包”选项来执行 ，如下图所示。
After executing the package all the data from the Excel worksheet will be imported in selected database table. The image on the right side represents imported data into the SQL database, from the Excel worksheet to the left
执行该程序包后，Excel工作表中的所有数据都将导入到选定的数据库表中。 右侧的图像表示从Excel工作表到左侧的导入到SQL数据库的数据。
Using this method, data from the same excel sheet can be imported in the selected table until there is no modifications in excel with data types. If the data is inserted or overwritten with new ones, importing process will be successfully accomplished, and the data will be added to the table in SQL database
使用此方法，可以将来自相同excel工作表的数据导入所选表中，直到excel中没有对数据类型进行任何修改为止。 如果插入或覆盖了新数据，则导入过程将成功完成，并将数据添加到SQL数据库的表中
有用的资源 (Useful resources)
- Import data from excel to SQL server 将数据从Excel导入到SQL Server
- SSIS packages for data importing 用于数据导入的SSIS包
翻译自: https://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/
ssis 导入excel