将多个平面文件导入到多个SQL表 [英] Import multiple flat files to multiple SQL Tables
问题描述
这是我的文件夹设置.
这是文件设置
想法是遍历文件夹&将FileA的内容放到数据库中的Table FileA.dbo中(还有FileB,FileC等).在所有文件夹中,FileName结构都是相同的.
The idea is to traverse through the folders & put FileA contents to Table FileA.dbo on the database ( also FileB,FileC etc). The FileName structure is same throughout all the folders.
我有这个ssis软件包,我在其中使用foreachloop->数据流解析文件夹.
I have this ssis package where I parse through the folders with a foreachloop-> dataflow.
我检查了为获取文件名而制定的算法是否有效
I have checked that the algorithm I've formulated to get the filename is working
REVERSE(Substring(Reverse( @[User::FileName] ),5,LEN( @[User::FileName] ))) == "FileA"
它解析出.txt扩展名.下面是我拥有的foreach循环的设置.
It parses out the .txt extension. Below is the setup of the foreach loop I have.
要启动整个程序包,我在文件夹&中放置了.txt文件.我将foreach容器循环子文件夹.
To start this whole package I've .txt files inside the folder & I make the foreach container loop subfolders.
由于我不是真正的SSIS软件包开发人员,所以这是我可以做一些研究的最好方法.我遇到的问题是,它似乎可以部分起作用.
Since, I'm not really a SSIS package developer, this is the best I could do with some research. The problem I'm running into is, it seems to work partially.
快照是我所拥有的方案的模型,实际上我有200多个文件夹,其中包含50个文本文件,每个文件都指定将内容转储到相应的命名表中.
The snapshots are mockups of the scenario I have and in reality I have over 200 folders with 50 text files each designated to dump the contents to respective named tables.
但是在包成功执行之后,我看到的总行数非常少&可能不正确.无论如何,它是否可以获得其遍历的文件夹数量的计数/列表.另外,我做错什么了吗?
But the total number of rows I'm seeing after the package successfully executes is extremely low & can't be right. Is there anyway to get a count/list of the number of folders it traversed. Also, am I doing something wrong ?
理想情况下,我只是不想从.txt文件开始,而是将整个内容放在文件夹中,获取文件名(我认为我有一个工作代码)&只需将所有信息转储到OLEDB目的地.
Ideally, I'd just like to not have to start with .txt files but just have the whole thing go to folders, get the filenames ( which I think I've a working code for) & just dump all the info to the OLEDB Destinations.
任何帮助,都非常感谢链接到资源.
Any help, link to resources is much appreciated.
推荐答案
解决方案概述
您可以在foreach循环中使用一个DataFlow Task来实现此目的,但诀窍是您必须从变量读取源平面文件名和Destination SQL表名
Solution overview
You can achieving this using one DataFlow Task inside the foreach loop, but the trick is that you have to Read source flat file name and the Destination SQL Table name from variables
注意:平面文件结构必须相同,并且SQL表必须具有相同的结构
- 右键单击
Control Flow
窗口,然后单击Variables
- Right Click on the
Control Flow
window and click onVariables
-
声明2个SSIS变量:
Declare 2 SSIS variables:
-
FlatFilename
:类型为String
,并为默认值分配一个随机文件路径(即C:\MockFolder\FileA.txt
) - 'SQLTablename
: of type
String`并分配给以下表达式:
FlatFilename
: of typeString
and assign a default value a random file path (i.e.C:\MockFolder\FileA.txt
)- 'SQLTablename
: of type
String` and assign to the following expression:
这是假定所有目标表都具有相同的架构dbo
"[dbo].[" + REPLACE(RIGHT( @[User::FlatFilename] , FINDSTRING(REVERSE( @[User::FlatFilename] ) , "\\", 1) - 1),".txt","") + "]"
- 在其中添加
Foreach Loop Container
和DataFlow Task
,单击DataFlow Task
,然后在属性选项卡上,将Delay Validation
属性设置为True
- Add a
Foreach Loop Container
and aDataFlow Task
inside it, click on theDataFlow Task
and on the properties Tab, Set theDelay Validation
property toTrue
- 双击
Foreach Loop container
并选择主目录,然后文件过滤器*.txt
也选择fully qualified
检索文件名选项
- Double Click on the
Foreach Loop container
and select the main Directory , and the files filter*.txt
also choose thefully qualified
retrieve file name option
- 转到变量映射选项卡,然后选择
@[User::FlatFilename]
变量
-
添加2个连接管理器
Add 2 Connection manager
- FlatFileConnection:一个平面文件连接管理器,并通过随机选择
File
(即C:\MockFolder\FileA.txt
) 对其进行配置
- OLEDBConnection:一个OLEDB连接管理器,并将其配置为目标SQL Server数据库
- FlatFileConnection: a Flat File connection manager, and configure it by selecting randomly a
File
(i.e.C:\MockFolder\FileA.txt
) - OLEDBConnection: an OLEDB connection manager, and configure it to your Destination SQL Server Database
在DataFlow Task
中,添加Flat File Source
和OLEDB Destination
,在OLEDB Destination
中选择Table name from variable
选项,然后选择@[User::SQLTablename]
作为变量名
In The DataFlow Task
, add a Flat File Source
and an OLEDB Destination
, in the OLEDB Destination
select Table name from variable
option and select @[User::SQLTablename]
as the variable name
-
在源和目标之间映射列
Map columns between source and Destination
在连接管理器窗口中单击FlatFileConnection
,按F4
以显示属性选项卡,单击表达式"
Click on the FlatFileConnection
in the connection manager windows, press F4
to show the properties Tab, click on Expressions
-
选择
Connection String
属性,为其分配以下表达式:
Select the
Connection String
property assign to it the following expression:
@[user::FlatFilename]
这篇关于将多个平面文件导入到多个SQL表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!