将文件名存储在变量中并在 SSIS 中使用文件名创建表 [英] Store filename in variable and create tables with the filename in SSIS
问题描述
我在 SSIS 的一个文件夹中有几个 excel 源文件.我想从这些 excel 文件中提取数据并加载到 SQL 表中.我的问题是我想一一保存所有文件名，并想创建与文件名完全相同的 SQL 表然后想加载相应表中的每个excel文件.请帮助我如何为此创建一个包.
I've few excel source files in one folder in SSIS. I want to pull data from these excel files and load in to SQL tables. My problem is I want to save all the files names one by one and want to create SQL table with exactly same name as filename and then want to load each excel file in corresponding table. Please help me how to create a package for this.
推荐答案
Jayvee 提供了足够好的高级视图！让我补充一点细节.
Jayvee has presented the high level view which is good enough! Let me add in bit detail.
我假设您有动态 Excel 文件连接.
I am assuming that you have dynamic Excel file connection.
声明一个变量并将其命名为FileName.并为其分配文件夹中可用的第一个文件名.
Declare a variable and named it as FileName. And assign it the first file name which is available in the folder.
放置Foreach 循环容器并双击它.指定文件夹:和文件:，如下图所示.
Place Foreach Loop Container and double click on it. Specify the Folder: and Files: as shown in image below.
在同一个Foreach Loop Editor中，转到变量映射.从下拉列表中选择变量.这与我们在第一步中定义的变量相同.将其索引设置为0.点击确定.
In the same Foreach Loop Editor, go to Variable Mappings. Select Variable from drop down list. This is the same variable which we defined in first step. Set its Index to 0. Click OK.
剩下的任务和 Jayvee 解释的一样.
Remaining task is same as Jayvee explained.
参见 此链接以获得进一步帮助.这对于结果集属性未正确设置.我认为将 ResultSet 属性设置为 SingleRow 就可以了.
See this link for further help. And this for Result Set Property Not Set Correctly. I think setting ResultSet property to SingleRow will do the job.
这篇关于将文件名存储在变量中并在 SSIS 中使用文件名创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
