如何将名称和模式相同但目录不同的文本文件导入数据库? [英] How to import text files with the same name and schema but different directories into database?
问题描述
我需要将多个具有相同名称和相同架构的 txt 文件导入到 SQL Server 2008 数据库中的同一个表中.我的问题是它们都在不同的目录中:
I require to import multiple txt files with the same name and same schemas into the same table in SQL Server 2008 database. The problem that I have is that they are all in different directories:
TEST
201304
sample1.txt
sample2.txt
201305
sample1.txt
sample2.txt
201306
sample1.txt
sample2.txt
在 SSIS 中有什么方法可以设置吗?
Is there any way in SSIS that I can set this up?
推荐答案
是的.您将需要使用 Foreach 文件容器，然后检查 Traverse 子文件夹选项.
Yes. You will want to use a Foreach File Container and then check the Traverse Subfolder option.
显然我的回答不够丰富，所以请接受这个工作代码，它说明了我简短的原始回答所说的内容.
Apparently my answer wasn't cromulent enough, so please accept this working code which illustrates what my brief original answer stated.
我如上所述创建了 3 个文件夹来包含文件 sample1.txt 和 sample2.txt
I created 3 folders as described above to contain files sample1.txt and sample2.txt
C:>MKDIR SSISDATASOTEST201304
C:>MKDIR SSISDATASOTEST201305
C:>MKDIR SSISDATASOTEST201306
文件内容如下.每个文件夹中文件的每个版本都有递增的 ID 值以及更改的文本值，以证明它已选择新文件.
The contents of the file are below. Each version of the file in each folder has the ID value incremented along with the text values altered to prove it has picked up the new file.
ID,value
1,ABC
包生成
这部分假设您已安装 BIDS Helper.解决方案不需要它，只是提供了一个通用框架，未来的读者可以用来重现这个解决方案
Package generation
This part assumes you have BIDS Helper installed. It is not required for the solution but simply provides a common framework future readers could use to reproduce this solution
我创建了一个包含以下内容的 BIML 文件.即使我在那里有表创建步骤，我也需要在生成包之前在目标服务器上运行它.
I created a BIML file with the following content. Even though I have the table create step in there, I needed to have that run on the target server prior to generating the package.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<!-- Create a basic flat file source definition -->
<FileFormats>
<FlatFileFormat
Name="FFFSrc"
CodePage="1252"
RowDelimiter="CRLF"
IsUnicode="false"
FlatFileType="Delimited"
ColumnNamesInFirstDataRow="true"
>
<Columns>
<Column
Name="ID"
DataType="Int32"
Delimiter=","
ColumnType="Delimited"
/>
<Column
Name="value"
DataType="AnsiString"
Delimiter="CRLF"
InputLength="20"
MaximumWidth="20"
Length="20"
CodePage="1252"
ColumnType="Delimited"
/>
</Columns>
</FlatFileFormat>
</FileFormats>
<!-- Create a connection that uses the flat file format defined above-->
<Connections>
<FlatFileConnection
Name="FFSrc"
FileFormat="FFFSrc"
FilePath="C:ssisdatasoTEST201306sample1.txt"
DelayValidation="true"
/>
<OleDbConnection
Name="tempdb"
ConnectionString="Data Source=localhostdev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
/>
</Connections>
<!-- Create a package to illustrate how to apply an expression on the Connection Manager -->
<Packages>
<Package
Name="so_19957451"
ConstraintMode="Linear"
>
<Connections>
<Connection ConnectionName="tempdb"/>
<Connection ConnectionName="FFSrc">
<Expressions>
<!-- Assign a variable to the ConnectionString property.
The syntax for this is ConnectionManagerName.Property -->
<Expression PropertyName="FFSrc.ConnectionString">@[User::CurrentFileName]</Expression>
</Expressions>
</Connection>
</Connections>
<!-- Create a single variable that points to the current file -->
<Variables>
<Variable Name="CurrentFileName" DataType="String">C:ssisdatasoTEST201306sample1.txt</Variable>
<Variable Name="FileMask" DataType="String">*.txt</Variable>
<Variable Name="SourceFolder" DataType="String">C:ssisdatasoTEST</Variable>
<Variable Name="RowCountInput" DataType="Int32">0</Variable>
<Variable Name="TargetTable" DataType="String">[dbo].[so_19957451]</Variable>
</Variables>
<!-- Add a foreach file enumerator. Use the above -->
<Tasks>
<ExecuteSQL
Name="SQL Create Table"
ConnectionName="tempdb">
<DirectInput>
IF NOT EXISTS (SELECT * FROM sys.tables T WHERE T.name = 'so_19957451' and T.schema_id = schema_id('dbo'))
BEGIN
CREATE TABLE dbo.so_19957451(ID int NOT NULL, value varchar(20) NOT NULL);
END
</DirectInput>
</ExecuteSQL>
<ForEachFileLoop
Name="FELC Consume files"
FileSpecification="*.csv"
ProcessSubfolders="true"
RetrieveFileNameFormat="FullyQualified"
Folder="C:"
ConstraintMode="Linear"
>
<!-- Define the expressions to make the input folder and the file mask
driven by variable values -->
<Expressions>
<Expression PropertyName="Directory">@[User::SourceFolder]</Expression>
<Expression PropertyName="FileSpec">@[User::FileMask]</Expression>
</Expressions>
<VariableMappings>
<!-- Notice that we use the convention of User.Variable name here -->
<VariableMapping
Name="0"
VariableName="User.CurrentFileName"
/>
</VariableMappings>
<Tasks>
<Dataflow Name="DFT Import file" DelayValidation="true">
<Transformations>
<FlatFileSource Name="FFS Sample" ConnectionName="FFSrc"/>
<RowCount Name="RC Source" VariableName="User.RowCountInput"/>
<OleDbDestination
Name="OLE_DST"
ConnectionName="tempdb">
<TableFromVariableOutput VariableName="User.TargetTable"/>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</ForEachFileLoop>
</Tasks>
</Package>
</Packages>
</Biml>
右键单击 biml 文件并选择 Generate SSIS Package.此时，您应该有一个名为 so_19957451 的包添加到您当前的 SSIS 项目中.
Right click on the biml file and select Generate SSIS Package. At this point, you should have a package named so_19957451 added to your current SSIS project.
不需要任何配置，因为它已经通过 BIML 完成，但是 moar 屏幕截图可以提供更好的答案.
There's no need for any configuration because it's already been done via BIML but moar screenshots make for better answers.
这是基本包
这是我的变量
Foreach 循环的配置，如 MSDN 文章以及我选择 Traverse 子文件夹的说明中所述
Configuration of the Foreach Loop, as called out in the MSDN article as well as my note of select the Traverse subfolder
将每个循环生成的值分配给变量 Current
Assign the value generated per loop to the variable Current
平面文件源有一个应用于 ConnectionString 属性的表达式，以确保它使用变量 @User::CurrentFileName.这会更改每次执行循环的源.
The flat file source has an expression applied to the ConnectionString property to ensure it uses the Variable @User::CurrentFileName. This changes the source per execution of the loop.
来自数据库的结果
匹配包执行的输出
信息:DFT 导入文件中的 0x402090DC，FFS 示例 [2]:文件C:ssisdatasoTEST201304sample1.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201304sample1.txt" has started.
信息:0x402090DD at DFT 导入文件，FFS 示例 [2]:文件C:ssisdatasoTEST201304sample1.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201304sample1.txt" has ended.
信息:0x402090DC at DFT 导入文件，FFS 示例 [2]:文件C:ssisdatasoTEST201304sample2.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201304sample2.txt" has started.
信息:0x402090DD 在 DFT 导入文件，FFS 示例 [2]:文件C:ssisdatasoTEST201304sample2.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201304sample2.txt" has ended.
信息:DFT 导入文件中的 0x402090DC，FFS 示例 [2]:文件C:ssisdatasoTEST201305sample1.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201305sample1.txt" has started.
信息:0x402090DD at DFT 导入文件，FFS 示例 [2]:文件C:ssisdatasoTEST201305sample1.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201305sample1.txt" has ended.
信息:0x402090DC at DFT 导入文件，FFS 示例 [2]:文件C:ssisdatasoTEST201305sample2.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201305sample2.txt" has started.
信息:DFT 导入文件中的 0x402090DD，FFS 示例 [2]:文件C:ssisdatasoTEST201305sample2.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201305sample2.txt" has ended.
信息:DFT 导入文件中的 0x402090DC，FFS 示例 [2]:文件C:ssisdatasoTEST201306sample1.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201306sample1.txt" has started.
信息:DFT 导入文件中的 0x402090DD，FFS 示例 [2]:文件C:ssisdatasoTEST201306sample1.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201306sample1.txt" has ended.
信息:DFT 导入文件中的 0x402090DC，FFS 示例 [2]:文件C:ssisdatasoTEST201306sample2.txt"的处理；已经开始.
Information: 0x402090DC at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201306sample2.txt" has started.
信息:DFT 导入文件中的 0x402090DD，FFS 示例 [2]:文件C:ssisdatasoTEST201306sample2.txt"的处理；结束了.
Information: 0x402090DD at DFT Import file, FFS Sample [2]: The processing of file "C:ssisdatasoTEST201306sample2.txt" has ended.
这篇关于如何将名称和模式相同但目录不同的文本文件导入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
