解决方案概述

您可以在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表必须具有相同的结构

1. 右键单击Control Flow窗口，然后单击Variables

1. Right Click on the Control Flow window and click on Variables

1. 声明2个SSIS变量:

1. Declare 2 SSIS variables:

• FlatFilename:类型为String，并为默认值分配一个随机文件路径(即C:\MockFolder\FileA.txt)
• 'SQLTablename : of type String`并分配给以下表达式:

• FlatFilename : of type String and assign a default value a random file path (i.e. C:\MockFolder\FileA.txt)
• 'SQLTablename: of typeString` and assign to the following expression:

这是假定所有目标表都具有相同的架构dbo

 "[dbo].[" + REPLACE(RIGHT( @[User::FlatFilename] , FINDSTRING(REVERSE( @[User::FlatFilename]  ) , "\\", 1) - 1),".txt","") + "]"

1. 在其中添加Foreach Loop Container和DataFlow Task，单击DataFlow Task，然后在属性选项卡上，将Delay Validation属性设置为True

1. Add a Foreach Loop Container and a DataFlow Task inside it, click on the DataFlow Task and on the properties Tab, Set the Delay Validation property to True

1. 双击Foreach Loop container并选择主目录，然后文件过滤器*.txt也选择fully qualified检索文件名选项

1. Double Click on the Foreach Loop container and select the main Directory , and the files filter *.txt also choose the fully qualified retrieve file name option

1. 转到变量映射选项卡，然后选择@[User::FlatFilename]变量

1. 添加2个连接管理器

1. 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

1. 在源和目标之间映射列

1. 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

1. 选择Connection String属性，为其分配以下表达式:

1. Select the Connection String property assign to it the following expression:

@[user::FlatFilename]