How to import multiple text files into SQL Server with SSIS? MS BI

Import multiple csv text files into a SQL Server table with an SSIS dynamic loop.

This tutorial explains how to import multiple text files into SQL Server with SSIS using a single package and a dynamic loop to detect flat files. To start with, all text files (.txt, .csv, etc…) must be in the same folder. This automatic integration uses dynamic variables and allows to integrate many files with the same structure at the same time. To load several flat files dynamically, use an SSIS loop, i.e. the SSIS Foreach Loop Container component.

This package uses the same components as the previous SSIS tutorial to import a single flat file into a SQL table. It is therefore the same data flow used, but this time with variables in the dynamic file path.

Table of Contents

Step 1: Download the text files to import with SSIS and create the target table

First, download the three example files:

Customers_Data_1.csvTélécharger

Customers_Data_2.csvTélécharger

Customers_Data_3.csvTélécharger

The files are stored like this:

C:\data, contains these CSV files:
- Customers_Data_1.csv
- Customers_Data_2.csv
- Customers_Data_3.csv

Then create the destination table. The table uses the same structure as the tutorial for loading a flat file and fills a column with the full path to the source file.

IF NOT EXISTS (
   SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'[dbo].[Customers_with_filename]') 
      AND type in (N'U')
)
CREATE TABLE [dbo].[Customers_with_filename](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   [filename] [nvarchar](50) NULL,
   CONSTRAINT [CustomerswithFileNamePKCustomerID] 
      PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
GO

Step 2: Create the file loop and the SSIS data flow

First, use a loop from the control flow tab. Then select the dynamic Foreach Loop Container and drag it onto the workspace.

Use the SSIS Foreach Loop container and add the Data flow task to import multiple text files into a table with SSIS — Use the SSIS Foreach Loop container and add the Data flow task

Step 3: Create the SSIS dynamic variable to store the file path

Create a variable named FlatFilePath of type String at the package level. To display the variables, click on the package and press F4 or right-click in the data flow area. Here the variable is called FlatFilePath and the Data Type is String.

*Create the FlatFilePath SSIS variable to store text file names dynamically*

Set up the flat file connection as detailed in this tutorial with a data flow to load a flat file. It is the same principle but this time the dataflow allows to load several text files with a single SSIS package. Then modify the ConnectionString expression to use the dynamic variable instead of the hard path.

Modify the properties of the connection to the text file to import in the SSIS loop — Modify the properties of the connection to the flat file

Use the variable created before so that at each passage in the loop, the file name is dynamic.

Map the FlatFilePath SSIS variable to the ConnectionString property in the text files properties — Map the FlatFilePath variable to the ConnectionString property

Step 4: Configure the SSIS Foreach File Loop container

Then double-click on the Foreach Loop Container sequence. In the Collection tab :

Choose the folder where the flat files are located: C:\Data
Then specify the name of the flat files to be loaded with SSIS: Customers_Data*.csv
Choose the full name of the file with the path: Fully qualified

*Set the folder and filter to select the text files to be used in the SSIS loop*

Then, in the Variable Mappings tab, link the FlatFilePath variable to index 0. This link dynamically transmits the name of the current file to the variable and therefore to the file connection.

*Bind the SSIS variable dynamically to the current file name*

Step 5: Add the file name to import in an SSIS derived column

Edit the derived column component and add the following two derived columns for:

Add the Customer ID in integer format
Add the source file name from the dynamic variable

Add the CustomerID and the Filename as derived columns

Step 6. Run the SSIS package to import the multiple text files with SSIS and check data in SSMS

Now start the loading. The SSIS package loads the flat files into the database one by one, i.e., one data flow executed per file detected in the source folder. In this case, 3 iterations occur.

Execute the SSIS loop to import the text files into a single table and add the filename — *Execute the SSIS loop to import the text files into a single table*

Clicking in the data flow, we notice that 16 lines are integrated in each iteration.

*Lines imported with the Integration Services data flow*

Finally, check the results with the 2 following SQL queries. The result is consistent with 16 rows embedded for each source file, with no errors.

SELECT [Filename], COUNT(*)
  FROM [dbo].[Customers_with_filename]
  GROUP BY [Filename]; 

SELECT * 
  FROM [dbo].[Customers_with_filename];

Execute the SQL query in SSMS to check the data in the table

Conclusion on multiple text files import with SSIS

To avoid errors when importing of multiple text files into a SQL Server table with SSIS, check the encoding of the files to process. Also, the mapping of the variable is mandatory to make the package dynamic and avoid manual development.

More on data import and export with SSIS

SSIS Tutorials for beginners