This SSIS Integration Services article shows how to load data from Multiple CSV, Text, or flat files to the SQL Server table using the Foreach loop File enumerator with an example.
To demonstrate the SSIS Load Multiple Flat Files to SQL Server, we use the below-shown text files; our job is to export them to a table.
SSIS Load Multiple Flat Files to SQL Server Example
Drag and drop the Foreach loop container into the control flow region. Next, we created two variables, FolderPath and FileName, of string data type to store the folder path and file names.
Double-click on it will open the editor. First, change the SSIS Enumerator type to Foreach File Enumerator. Next, click the browse button beside the Expression. For more Data Loading options >> Click Here.
Under the Property Expressions Editor window, choose Directory as the property and click Expression. Then, drag User::FolderPath to Expression within the Expression Builder and click OK.
Click Ok to Close the Property Expressions Editor window.
In this SSIS Load Multiple Flat Files to SQL Server example, we want to load all the text files. So, use *.txt and select the Name and Extension option to retrieve the name along with the extension.
Under the Variable mappings tab, select the User::FileName variable. It assigns all the file names with the txt extension to this User::FileName variable.
Add SSIS data Flow Task to Foreach loop container to Load Multiple Flat Files to SQL Server table.
Double-click to open the SSIS Data Flow Region. Then, add the flat file source and double-click on it to open the Source Editor.
Click the New button to open the Flat File Connection Manager Editor window. Next, click the Browse button to choose any one of the text files to map the input and output columns.
Checkmark the Column Names in the first data row option if the file’s first row has header names.
Use the advanced section to change the Data type. Here, the column data type must match the destination table; otherwise, it throws an error.
Next, drag the OLE DB Destination and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing.
Select the table from the list. If not, click the new button to create a new one.
Next, go to the Mappings tab to check the input and available destination column mapping.
Select the Flat File Connection manager and Go to the Properties window. Next, click the browse button beside the Expressions.
Choose the ConnectionString as the Property Click the Expression and write the Expression. Here, we are building the pathway combining or concat the FolderPath and FileName variables.
Click Ok to Close the Property Expressions Editor window.
Run the SSIS Load Multiple Flat Files data to the SQL Server table using the Foreach Loop Enumerator package.
Now you can all four file’s data in a single SQL table.
