编辑

显然我的回答并不充分,所以请接受这个工作代码,说明我简短的原始答案.

来源数据

我创建了3个文件夹,如上所述,包含文件sample1.txt和sample2.txt

C:\>MKDIR SSISDATA\SO\TEST\201304
C:\>MKDIR SSISDATA\SO\TEST\201305
C:\>MKDIR SSISDATA\SO\TEST\201306

该文件的内容如下.每个文件夹中的每个文件版本都会增加ID值以及更改的文本值,以证明它已经拾取了新文件.

ID,value
1,ABC

包生成

本部分假设您已安装BIDS Helper.它不是解决方案所必需的,而是简单地提供了未来读者可以用来重现此解决方案的通用框架

我创建了一个包含以下内容的BIML文件.即使我有表创建步骤,我需要在生成包之前在目标服务器上运行.

<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:\ssisdata\so\TEST\201306\sample1.txt"
            DelayValidation="true"
        />
        <OleDbConnection
            Name="tempdb"
            ConnectionString="Data Source=localhost\dev2012;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:\ssisdata\so\TEST\201306\sample1.txt</Variable>
                <Variable Name="FileMask" DataType="String">*.txt</Variable>
                <Variable Name="SourceFolder" DataType="String">C:\ssisdata\so\TEST</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项目中.

包配置

没有任何配置,因为它已经通过BIML完成,但是moar截图可以提供更好的答案.

这是基本包

这是我的变量

配置Foreach循环,如MSDN文章中所述,以及我选择Traverse子文件夹的注释

将每个循环生成的值分配给变量Current

平面文件源具有应用于ConnectionString属性的表达式,以确保它使用Variable @User :: CurrentFileName.这会更改每次执行循环的源.

执行结果

数据库的结果

匹配包执行的输出

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201304\sample1.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201304\sample1.txt” has ended.

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201304\sample2.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201304\sample2.txt” has ended.

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201305\sample1.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201305\sample1.txt” has ended.

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201305\sample2.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201305\sample2.txt” has ended.

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201306\sample1.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201306\sample1.txt” has ended.

Information: 0x402090DC at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201306\sample2.txt” has started.

Information: 0x402090DD at DFT Import file,FFS Sample [2]: The processing of file “C:\ssisdata\so\TEST\201306\sample2.txt” has ended.