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-clickin