This article explores an SSIS package for importing multiple Excel files data into SQL Server tables.
本文探讨了用于将多个Excel文件数据导入SQL Server表的SSIS包。
介绍 (Introduction)
Recently I come across a situation in which third-party vendors send multiple Excel files to load data into SQL Server tables. We can use SSMS Import and Export Wizards for data import. You can read more about this in How to import/export data to SQL Server using the SQL Server Import and Export Wizard.
最近，我遇到一种情况，第三方供应商发送多个Excel文件以将数据加载到SQL Server表中。 我们可以使用SSMS导入和导出向导进行数据导入。 您可以在如何使用SQL Server导入和导出向导将数据导入/导出到SQL Server中阅读有关此内容的更多信息。
Suppose you received multiple Excel files (let’s say 50) for data import. You need to launch an import wizard 50 times and complete it. It is a manual, tedious, and time-consuming task.
假设您收到了多个Excel文件（比如说50个）用于数据导入。 您需要启动导入向导50次并完成导入。 这是一项手动，繁琐且耗时的任务。
As I started earlier in my articles, SSIS is a true friend for DBA and developers. It comes for rescue in such situations. Let’s explore the SSIS solution in this article.
正如我在文章前面所提到的，SSIS是DBA和开发人员的真正朋友。 在这种情况下可以进行救援。 让我们探索本文中的SSIS解决方案。
样本数据 (Sample data)
For this article, I want data import from the following Excel files:
对于本文，我希望从以下Excel文件导入数据：
Each Excel file contains one row, and source column shows the excel file name:
每个Excel文件包含一行，并且源列显示excel文件名：
先决条件 (Prerequisites)
SQL Server Data Tools or Visual Studio SQL Server数据工具或Visual Studio SQL Server instance SQL Server实例创建一个SSIS包，用于从多个Excel文件导入数据 (Create an SSIS package for the data import from multiple Excel files)
First, we will create an SSIS package for importing a single Excel file data into the SQL Server table. Later, we will convert the same package and import multiple Excel files data using SSIS variables and parameters.
首先，我们将创建一个SSIS包，用于将单个Excel文件数据导入到SQL Server表中。 稍后，我们将转换相同的包并使用SSIS变量和参数导入多个Excel文件数据。
创建用于数据导入SQL表 (Create a SQL table for data import)
We require a SQL table that will have data from SSIS import operation. Execute the following script for creating a SQL table for data import:
我们需要一个SQL表，该表将包含来自SSIS导入操作的数据。 执行以下脚本来创建用于数据导入SQL表：
CREATE TABLE [dbo].[SSISDataImport]([EmpID] [int] NULL,[EmpName] [varchar](50) NULL,[Source] [nvarchar](50) NULL) ON [PRIMARY]GO
添加源excel文件连接 (Add a Source excel file connection)
Open Visual Studio and create a new SSIS package project. InControl Flow, right-click and go toNew Connection:
打开Visual Studio并创建一个新的SSIS包项目。 在“控制流”中，右键单击并转到“新建连接”：
It shows all available connection types. Click onEXCELconnection manager and add it:
它显示所有可用的连接类型。 单击EXCEL连接管理器并添加它：
InExcel Connection Manager, provide the path of Excel file, and it automatically selects the Microsoft Excel versions. The first row of the Excel sheet contains the column header; therefore, we have a check on theFirst row has column namesoption:
在Excel Connection Manager中，提供Excel文件的路径，它会自动选择Microsoft Excel版本。 Excel工作表的第一行包含列标题； 因此，我们在“第一行具有列名”选项中进行了检查：
ClickOK, and it shows the Excel connection in the connections manager area:
单击OK，它在连接管理器区域中显示Excel连接：
添加目标OLE DB连接 (Add a destination OLE DB connection)
Now, add a destination OLE DB connection manager. Click on add new connection and select an OLE DB connection:
现在，添加目标OLE DB连接管理器。 单击添加新连接，然后选择一个OLE DB连接：
Add SQL Server instance name, authentication (windows\SQL) in the OLE DB connection manager:
在OLE DB连接管理器中添加SQL Server实例名称，身份验证（windows \ SQL）：
We have both source and destination connections in the connection manager window. We will use these connections in the data flow and control flow tasks of the SSIS package:
在连接管理器窗口中，我们同时具有源连接和目标连接。 我们将在SSIS包的数据流和控制流任务中使用这些连接：
添加数据流任务以将数据从Excel导入SQL表 (Add a data flow task for data import from Excel to SQL table)
Add aData Flow Taskin theControl Flow. This data flow task will flow data from the Excel files into SQL Server tables:
在控制流中添加数据流任务。 此数据流任务会将数据从Excel文件流到SQL Server表中：
RenameData Flow TasktoData import from multiple excels files. It is an optional step. However, it is a better approach to renaming the tasks. We can easily recognize the task desired behavior using the custom names:
将数据流任务重命名为从多个Excel文件导入数据。 这是一个可选步骤。 但是，这是重命名任务的更好方法。 我们可以使用自定义名称轻松识别任务所需的行为：
Double-click on this data flow task, and it takes you to theData Flowtab. Drag anExcel Sourceto the data flow. A red cross icon shows that configuration is required for this SSIS task:
双击此数据流任务，它将带您到“数据流”选项卡。 将Excel Source拖到数据流中。 红叉图标显示此SSIS任务需要配置：
Double-click on this excel source and inExcel Source Editor, select the Excel sheet that contains data. We should have data on similar sheets of all Excel files:
双击此excel源，然后在Excel Source Editor中，选择包含数据的Excel工作表。 我们应该在所有Excel文件的相似图纸上拥有数据：
Click onPreview,and we can get a glimpse of the Excel sheet data:
单击预览，我们可以一窥Excel工作表数据：
In the left-hand menu, click onColumnsand verify the Excel sheet columns. If we want excluding any column from data import, we can remove the checkmark from the particular column:
在左侧菜单中，单击“列”，然后验证Excel工作表列。 如果要从数据导入中排除任何列，则可以删除特定列中的复选标记：
Click onOK, and it shows the successful connection of an Excel Source:
单击“确定”，它显示成功连接了Excel Source：
Now, drag anOLE DB Destinationand connect it with the Excel Source using blue precedence constraint:
现在，拖动OLE DB目标并使用蓝色优先级约束将其与Excel Source连接：
Double-click onOLE DB Destinationand it opens theOLE DB Destination Editor. Specify the OLE DB connection and SQL table that we created earlier:
双击OLE DB目标，它会打开OLE DB目标编辑器。 指定我们之前创建的OLE DB连接和SQL表：
We need to verify the source and destination mapping. Click onMappingsand verify it:
我们需要验证源映射和目标映射。 单击映射并进行验证：
ClickOK, and it verifies the conversion error between Unicode and non-Unicode string data types:
单击“确定”，它会验证Unicode和非Unicode字符串数据类型之间的转换错误：
To resolve this, we need aData Conversiontask from the SSIS toolbox. Drag it from SSIS toolbox and connect it with the Excel Source:
要解决此问题，我们需要SSIS工具箱中的数据转换任务。 将其从SSIS工具箱中拖动，并将其与Excel Source连接：
Double-click onData Conversion. It opens theData Conversion Transformation Editor. Select theInput Column (Source)and change the data type asUnicode_string[D_WSTR]. We also change theEmpNamedata type asString[DT_STR]:
双击数据转换。 打开数据转换转换编辑器。 选择输入列（源）并将数据类型更改为Unicode_string [D_WSTR]。 我们还将EmpName数据类型更改为String [DT_STR]：
In the above screenshot, we also changed the output column alias. ClickOKand add precedence constraint from data conversion task to OLE DB Destination:
在上面的屏幕截图中，我们还更改了输出列别名。 单击确定，然后将优先级约束从数据转换任务添加到OLE DB目标：
We can still see a red cross on the OLE DB Destination task. It requires a configuration with the new columns that we derived from the data conversion.
我们仍然可以在OLE DB Destination任务上看到一个红叉。 它需要使用我们从数据转换派生的新列进行配置。
Open the OLE DB Destination Editor again and navigate to Mappings. In the Mapping, change the input columns, as shown below:
再次打开OLE DB目标编辑器，然后导航到“映射”。 在“映射”中，更改输入列，如下所示：
ClickOK. We can see that all configuration of the SSIS package is successful:
单击确定。 我们可以看到SSIS包的所有配置都成功：
Execute the package, and it shows successful data import from a single Excel file:
执行该程序包，它显示从单个Excel文件成功导入数据：
Now, execute the following TRUNCATE TABLE statement to configure SSIS package data import from multiple Excel sheets:
现在，执行以下TRUNCATE TABLE语句以配置从多个Excel工作表导入SSIS包数据：
TRUNCATE TABLE [SQLShack].[dbo].[SSISDataImport];
禁用SSIS项目调试 (Disable SSIS project debugging)
Right-click on the SSIS project and go to properties. In the debugging menu, disable theRun64BitRunTimeoption:
右键单击SSIS项目，然后转到属性。 在调试菜单中，禁用Run64BitRunTime选项：
ClickApplyandOKto save changes.
单击“应用”，然后单击“确定”保存更改。
在SSIS包中添加变量 (Add variables in SSIS package)
We will use SQL variables for defining multiple Excel sheets in a for each loop. Right-click in data flow and add variables.
我们将在每个循环中使用SQL变量定义多个Excel工作表。 右键单击数据流并添加变量。
Directory: It holds the path of all Excel files目录：保存所有Excel文件的路径ExcelPath: It holds the path of an Excel file along with file nameExcelPath：它包含一个Excel文件的路径以及文件名
Under theConnection Manager, click on theExcel Connection Managerand view its properties. Click onExpressions, as shown below:
在“连接管理器”下，单击“Excel连接管理器”并查看其属性。 点击Expressions，如下所示：
In theExpression Builder, expand theVariables and Parametersfolder.Drag the variable[User:: ExcelPath]to the expression and evaluate the expression. It should show the variable value we defined earlier:
在“表达式生成器”中，展开“变量和参数”文件夹。将变量[User :: ExcelPath]拖到表达式中并评估表达式。 它应该显示我们之前定义的变量值：
ClickOK, and you can see the variable in theProperty Expressions Editor:
单击“确定”，您可以在“属性表达式编辑器”中看到该变量：
ClickOK, and it shows a symbol “fx” in theExcel Connection Manager:
单击“确定”，它在Excel Connection Manager中显示符号“ fx”：
配置一个Foreach循环容器以从多个Excel文件导入数据 (Configure a Foreach Loop Container for data import from multiple Excel files)
Now, go back to Control Flow and drag a Foreach Loop Container. We use this container for starting a loop for the number of executions specified. You can read more about For Each loop in the Using SSIS ForEach Loop containers to process files in Date Order and SSIS Foreach Loop vs For Loop Container:
现在，返回“控制流”并拖动一个Foreach循环容器。 我们使用此容器为指定的执行次数启动循环。 您可以在使用SSIS ForEach循环容器以日期顺序处理文件以及SSIS Foreach循环与For循环容器中阅读有关For Each循环的更多信息：
Drag theData import from multiple excel filestask into the Foreach Loop Container:
将来自多个Excel文件的数据导入任务拖动到Foreach循环容器中：
Double-click on Foreach Loop Container, and it opens the following loop editor. Make the following changes:
双击Foreach循环容器，它将打开以下循环编辑器。 进行以下更改：
In theExpression, click on eclipse and specify the variableUser:: Directory.You can also evaluate an expression, and it should show the value of the variable, as shown below:
在表达式中，单击eclipse并指定变量User :: Directory。您还可以对表达式求值，它应该显示变量的值，如下所示：
Under theFolder, specify the path of the folder. We want data import from all files in this folder, so we have specified *.* in the files section:
在文件夹下，指定文件夹的路径。 我们希望从该文件夹中的所有文件导入数据，因此我们在文件部分中指定了*。*：
Now, click onVariable Mappingsand map theUser:: ExcelPathvariable as shown below. It automatically takes index value zero:
现在，单击“变量映射”并映射User :: ExcelPath变量，如下所示。 它会自动将索引值设为零：
ClickOK, and it completes the configuration of SSIS package for importing multiple files:
单击OK，它完成了用于导入多个文件的SSIS包的配置：
Before executing the package, let’s verify that our SQL table does not have any data:
在执行程序包之前，让我们确认我们SQL表没有任何数据：
Execute the SSIS package using theStartbutton:
使用“开始”按钮执行SSIS包：
SSIS Package is successful now. You can see a green tick icon on the Foreach Loop Container and data import task:
SSIS包现在成功。 您可以在Foreach循环容器和数据导入任务上看到一个绿色的勾号图标：
Let’s verify data in the SQL table. In the following screenshot, using the source table, we can verify that we have data from all Excel files. We have data fromEmployeeData-1toEmployeeData-10:
让我们验证SQL表中的数据。 在下面的屏幕快照中，使用源表，我们可以验证是否具有来自所有Excel文件的数据。 我们有从EmployeeData-1到EmployeeData-10的数据：
We should have a similar column structure in an Excel file for data import. You can configure additional tasks for error handling or capture bad data. Take reference of An overview of Error Handling in SSIS packages for it.
我们应该在Excel文件中具有类似的列结构以进行数据导入。 您可以配置其他任务以进行错误处理或捕获不良数据。 为此，请参考SSIS软件包中的错误处理概述 。
结论 (Conclusion)
This article explores the process of importing multiple Excel files into SQL Server table. It saves manual efforts and expedites the data import process with N number of files. You can schedule a SQL agent job for SSIS package execution for frequent tasks.
本文探讨了将多个Excel文件导入SQL Server表的过程。 使用N个文件，可以节省人工，并加快了数据导入过程。 您可以为频繁执行的任务计划SQL代理作业以执行SSIS包。
翻译自: /import-data-from-multiple-excel-files-in-sql-server-tables-using-an-ssis-package/
