This article demonstrate how to load multiples .txt or .csv files to a SQL Server table.
As you can see here we have two flat files named Sales1.txt and Sales2.txt under folder Sales.
File Sales1.txt has 4 records while Sales2.txt file has 2 records only.
Lets go step by step and see how to build a package to load all flat files into table that are present in folder Sales .
Here you can see, we have a table named SalesData. It has same number of columns that we have in flat files.
So the Data from all flat files will be loaded into this table.
Lets open SQL Server Data Tools and create a new SSIS package.
[How to create a first SSIS package]
After that, select and drag Foreach Loop Container into Control flow region, the Foreach loop container will reads the files present in Sales folder and loop through one file at a time and load the file data into SQL table.
Note: The Foreach Loop container repeats a control flow task in SSIS package.Â
Now, lets create a variable that will store the folder path from where we will be reading the files.
Lets create a new variable and assign it an input folder path as shown below.
Once you create the variable, next we will configure the Foreach Loop Container.
Just double click on Foreachloop Container, and you can see a Foreach Loop Editor window opens.
Now click on Collection tab, select Foreach File Enumerator from Enumerator property.
Here, we have selected Foreach File Enumerator because we want to loop through the files present in folder.
Next, we set the Expressions property. Just double click on ellipsis (…), and you can see a Property Expressions Editor window opens as shown below.
Select Directory in Property dropdown after that click on ellipsis (…) to provide the directory path.
In Expression Builder, expand the variables and Parameters folder under that you can see the variable that you created named InputFolderPath.
Just select the variable and drag to Expression window then click on Evaluated expression to validate the expression.
Once you click on Evaluated Expression you can see it displays the input folder path in Evaluated Value window which means expression is correct.
After that click on OK button.
After that close the Property Expression Editor window.
Next, you need to select the file name in Retrieve file name property and it has few options as follows:
- Name and Extension: If you select this option, the Foreach loop will store the file name and extension in the variable such as sales1.txt.
- Name Only: If you select this option, the Foreach loop will store the file name in the variable such as sales1.
- Fully Qualified: If you select this option, the Foreach loop store the path, file name, and extension in the variable such as E:\SSIS_Work\Sales\Sales1.txt.
- Traverse subfolders: If you want to check the subfolders, please checkmark this option.
Lets select the Name and Extension option as shown below.
Next, we have to store the file name and extension in a variable so that it can be used in Data Flow Task.
For this, just click on Variable Mappings tab then create a new variable as shown below.
Now you can see the newly created variable as shown below.
After that click on OK button.
Now, select Data Flow Task and drag it inside Foreach Loop Container as shown below.
Now double click on Data Flow Task, it opens the Data Flow tab.
After that, select Flat File Source and drag to Control flow region as shown below.
Now double click on Flat File Source to configure the connection manager settings.
You can see, Flat File Connection Manager Editor window opens. Now you need to provide a source file path.
At this moment you can select any file from folder later we will replace that file path with expression to get file names dynamically.
After that, just take a look at text qualifier, row delimiters and check the option column names in first row, if the first row has column names.
Next, click on columns tab to preview the column data and modify the row and column delimiters as shown below.
Next, In Advance tab, you can configure the column properties such as modify data type, column name, delimiter as shown below.
Next, In Preview tab you can see the preview of data. After that you can click OK button to close the Flat File Source Connection manager Editor.
Next, click on Flat File Source Editor columns tab to verify the columns. In this tab, you can uncheck the unwanted columns that you do not want.
Now just click on OK button to close the Flat File Source Editor.
Next, we will select the OLEDB destination and drag it to control flow region then connect Flat file source to OLEDB destination as shown below.
Now double click on OLEDB destination to configure the connection manager settings.
Next, click on the Mappings tab to check whether the source columns exactly mapped to the destination columns. Columns names are mapped automatically by default.
If you have different names at source and different names at destination then columns will not map automatically then you have to mapped them manually.
To map them manually, just select source column and drag it over destination column.
After that, click on OK button.
So far, we have configured Foreach Loop container and Data flow task to load the data from source to destination.
Now we have to change the connection string settings of a Flat File Connection Manager.
If you remember, when we configured the flat file connection setting we selected a single file from folder.
But, our requirement is to load multiple files means all the files those are present in that folder that means we need to assign the file names dynamically.
Now select the Expressions from Properties window as shown below.
After that, you will see a Property Expressions Editor window opens, Just select the ConnectionString then click on Expression.
You will see a Expression Builder window opens. Here just select the InputFolderPath variable and drag it into expression window then add +”\\”+ and after that just drag FileName variable as shown below.
After that click on Evaluate Expression to validate the Expression.
Next click on OK to close the Expression Builder window, then again click on OK button to close the Property Expressions Editor window.
Now, execute the package and you can see package gets executed successfully.
Lets check the table, and you can see data from source files have been loaded.
There are total 6 rows, means 4 rows from sales1.txt file and 2 rows from sales2.txt file.
Also Read..
SSIS – FTP task Send File to FTP Server
SSIS – FTP Task Send Multiple Files To FTP Server
