How to import text files with the same name and schema but different directories into database?

by

Yes. You will want to use a Foreach File Container and then check the Traverse Subfolder option.

Edit

Apparently my answer wasn’t cromulent enough, so please accept this working code which illustrates what my brief original answer stated.

Source data

I created 3 folders as described above to contain files sample1.txt and sample2.txt

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

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

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

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:\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>

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.

Package configuration

There’s no need for any configuration because it’s already been done via BIML but moar screenshots make for better answers.

This is the basic package

Control flow

Here are my variables

Variables

Configuration of the Foreach Loop, as called out in the MSDN article as well as my note of select the Traverse subfolder

Traverse Subfolder

Assign the value generated per loop to the variable Current

assign variables

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.

enter image description here

Execution results

Results from the database

enter image description here

Match the output from the package execution

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.

More Related Contents:

  1. to select all the employees who have joined most recently under king
  2. Unpivot with column name
  3. How can I delete using INNER JOIN with SQL Server?
  4. Is there a way to access the “previous row” value in a SELECT statement?
  5. What this query does to create comma delimited list SQL Server?
  6. Convert varchar into datetime in SQL Server
  7. sql query to return differences between two tables
  8. SQL Joins Vs SQL Subqueries (Performance)?
  9. Check if a row exists, otherwise insert
  10. SQL query to insert datetime in SQL Server
  11. How to do pagination in SQL Server 2008
  12. Convert Xml to Table SQL Server
  13. How to select the last record of a table in SQL?
  14. How to create table using select query in SQL Server?
  15. How do I remove redundant namespace in nested query when using FOR XML PATH
  16. Group DateTime into 5,15,30 and 60 minute intervals
  17. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  18. SQL SERVER: Get total days between two dates
  19. SQL Server 2008 – Help writing simple INSERT Trigger
  20. Store multiple bit values in a single table column
  21. Which SQL query is faster? Filter on Join criteria or Where clause?
  22. SQL Server equivalent to MySQL enum data type?
  23. Grant SELECT permission on a view, but not on underlying objects
  24. Using IF ELSE statement based on Count to execute different Insert statements
  25. Average of multiple columns
  26. Backup a single table with its data from a database in sql server 2008
  27. COALESCE Function in TSQL
  28. SQL Server equivalent to MySQL’s EXPLAIN
  29. Try_Convert for SQL Server 2008 R2
  30. SQL: Using DATEADD with bigints

Leave a Comment