如何将具有不同名称和相同架构的 Excel 文件导入数据库? [英] How to import Excel files with different names and same schema into database?
问题描述
如何将数据从每次都有不同文件名的excel源文件导入到SSIS中的sql server表中(示例excel文件名:abc123、123abc、ab123c等)
实现此目的的一种可能方法是使用 Control Flow
选项卡中的 ForEach Loop container
然后在控制流任务中放置一个数据流任务
.我已经在下面的例子中解释了这个过程.此示例使用 SQL Server
后端作为目标和 Excel 97-2003
格式 .xls
作为源文件.请注意Excel 文件的格式应相同.
分步流程:
创建一个名为
dbo.Location
的表,如屏幕截图 #1 所示.此示例将通过读取具有相同布局的三个不同 Excel 文件来填充此表.屏幕截图显示了包执行前的一个空表.在路径
c: emp
中创建两个 Excel 文件,如屏幕截图 #2 - #4 所示.请注意,这两个 Excel 文件具有相同的布局但内容不同.在 SSIS 包上,创建三个变量,如屏幕截图 #5 所示.变量
FolderPath
将包含 Excel 文件所在的路径;FileExtension
将包含 Excel 文件扩展名(在本例中为 *.xls)并且FilePath
应配置为指向一个有效的 Excel文件(仅在 Excel 连接管理器的初始配置期间需要此文件).在连接管理器中创建一个
Excel 连接
,指向一个有效的 Excel 文件,如屏幕截图 #6 所示.p>在连接管理器中创建一个指向 SQL Server 的
OLE DB 连接
.在 SSIS 包上,在 ForEach 循环容器中放置一个 ForEach 循环容器和一个数据流任务,如屏幕截图 #7 所示.
配置 ForEach 循环容器,如屏幕截图 #8 和 #9 所示.通过这样做,变量
User::FilePath
将在变量FolderPath的帮助下包含位于文件夹
c: emp
中的 Excel 文件的完整路径code> 和FileExtension
配置在Collection
部分.在数据流任务中,放置一个
Excel源
读取Excel文件数据和OLE DB目标
将数据插入SQL Server表dbo.位置.数据流任务应如屏幕截图 #10 所示.配置 Excel 源,如屏幕截图 #11 和 #12 所示,以使用 Excel 连接读取数据.
配置 OLE DB 目标,如屏幕截图 #13 和 #14 中所示,以将数据插入 SQL Server 数据库表中.
在连接管理器的 Excel 连接 上,配置表达式
ExcelFilePath
和ServerName
,如截图 #15.数据流任务的示例执行如屏幕截图 #16 所示.
屏幕截图 #17 显示包执行后表 dbo.Location 中的数据.请注意,它包含屏幕截图 #3 和 #4 中显示的 Excel 文件中的所有行.
在
Data Flow task
属性上,将DelayValidation
设置为 True,这样 SSIS 不会在您打开包装.
希望有所帮助.
屏幕截图 #1:
屏幕截图 #2:
屏幕截图 #3:
屏幕截图 #4:
屏幕截图 #5:
截图 #6:
屏幕截图 #7:
截图 #8:
屏幕截图 #9:
屏幕截图 #10:
屏幕截图 #11:
屏幕截图 #12:
屏幕截图 #13:
屏幕截图 #14:
屏幕截图 #15:
屏幕截图 #16:
屏幕截图 #17:
屏幕截图 #18:
How to import data into a sql server table in SSIS from an excel source file that has different file names each time (sample excel filenames: abc123, 123abc,ab123c etc.,)
One possible way of achieving this is by using ForEach Loop container
in the Control Flow
tab and then placing a Data Flow task
within the Control Flow task. I have explained the process in the below example. This example uses SQL Server
back-end as the destination and Excel 97-2003
format .xls
as the source files. Please note that the Excel files should be of same format.
Ste-by-step process:
Create a table named
dbo.Location
as shown in screenshot #1. This example will populate this table by reading three different Excel files having the same layout. The screenshot shows an empty table before the package execution.Create two Excel files in path
c: emp
as shown in screenshots #2 - #4. Notice that both the Excel files have the same layout but different content.On the SSIS package, create three variables as shown in screenshot #5. Variable
FolderPath
will contain the path where the Excel files are located;FileExtension
will contain the Excel file extension (here in this case it is *.xls) andFilePath
should be configured to point to one valid Excel file (this is required only during the initial configuration of the Excel connection manager).Create an
Excel connection
in the connection manager pointing to one valid Excel file as shown in screenshot #6.Create an
OLE DB Connection
in the connection manager pointing to the SQL Server.On the SSIS package, place a ForEach Loop container and a Data Flow task within the ForEach loop container as shown in screenshot #7.
Configure ForEach loop container as shown in screenshots #8 and #9. By doing this, variable
User::FilePath
will contain the full path Excel files located in the folderc: emp
with the help of variablesFolderPath
andFileExtension
configured on theCollection
section.Inside the data flow task, place an
Excel source
to read Excel file data and OLE DB destination
to insert data into SQL Server table dbo.Location. Data flow task should look like as shown in screenshot #10.Configure the Excel source as shown in screenshots #11 and #12 to read the data using Excel connection.
Configure the OLE DB destination as shown in screenshots #13 and #14 to insert the data into SQL Server database table.
On the Excel connection in the connection manager, configure the Expressions
ExcelFilePath
andServerName
as shown in screenshot #15.Sample execution of the data flow task is shown in screenshot #16.
Screenshot #17 displays the data in the table dbo.Location after package execution. Please note that it contains all the rows present in Excel files shown in screenshots #3 and #4.
On the
Data Flow task
properties, Set theDelayValidation
to True so that the SSIS doesn't throw errors when you open the package.
Hope that helps.
Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10:
Screenshot #11:
Screenshot #12:
Screenshot #13:
Screenshot #14:
Screenshot #15:
Screenshot #16:
Screenshot #17:
Screenshot #18:
这篇关于如何将具有不同名称和相同架构的 Excel 文件导入数据库?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!