How can SSIS efficiently import multiple text files with the same name and schema from different subfolders into a single database table?

Importing Text Files with the Same Name and Schema: Subfolder Traversal in SSIS

Challenge: Importing multiple text files with identical names and schemas into a single database table can be a hurdle when the files reside in separate directories.

Solution: In SQL Server Integration Services (SSIS), you can use the Foreach File Container to effortlessly tackle this challenge.

Foreach File Container:

This container iterates through a collection of files, applying a specified set of tasks to each one. By enabling the "Traverse Subfolder" option within the container, SSIS will recursively descend into subdirectories, processing all files that match the specified file mask.

Applying Expressions:

To dynamically modify the ConnectionString property of the Flat File Connection Manager during execution, leverage expressions. Assign the value of the current filename to the ConnectionString expression. This guarantees that the file source changes as the container loops through the files.

Variable Assignment:

Create a variable to represent the current file within the Foreach File Container. This allows subsequent tasks to access the file's path and execute necessary operations based on the current file being processed.

Data Flow Tasks:

Inside each iteration of the container, include a Data Flow task to process the imported data. This task consists of a Flat File Source, which reads the file, a Row Count transformation to count the number of input rows, and an OLE DB Destination, which loads the data into the target table.

Example Code:

Below is a more detailed code example to implement the solution:

<!-- C# code using System.IO to demonstrate a different approach -->
// Import System.IO for file I/O operations
using System.IO;

// Get the current directory
string currentDirectory = Directory.GetCurrentDirectory();

// Define the source data directory
string sourceDirectory = Path.Combine(currentDirectory, "SSISDATA\SO\TEST");

// Get all files with the specified extension
var files = Directory.GetFiles(sourceDirectory, "*.txt", SearchOption.AllDirectories);

// Iterate through each file
foreach (string file in files)
{
    // Get the file name without the extension
    string fileName = Path.GetFileNameWithoutExtension(file);

    // Load the data from the file into a data table
    DataTable data = GetDataFromFile(file);

    // Insert the data into the target table
    using (var connection = new SqlConnection("connection string"))
    {
        using (var command = new SqlCommand("INSERT INTO TargetTable (File, Data) VALUES (@File, @Data)", connection))
        {
            command.Parameters.AddWithValue("@File", fileName);
            command.Parameters.AddWithValue("@Data", data);

            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

The above C# code demonstrates an alternative approach using system-level capabilities to retrieve all files with the specified extension recursively from the source data directory and insert them into the target database table.

The above is the detailed content of How can SSIS efficiently import multiple text files with the same name and schema from different subfolders into a single database table?. For more information, please follow other related articles on the PHP Chinese website!