Requirement & Preface
In last post (SSIS Step by Step 002 - Output data to multiple flat files) , we output data from database to multiple flat files.
In this post, we're going to load data from multiple flat files to database table back.
We are going to learn:
1. How to use for each loop container in SSIS package.
2. How to use Foreach File Enumerator
Testing files are generated by (SSIS Step by Step 002 - Output data to multiple flat files), about the address table schema you can find them in SSIS Step by Step 001 - Load data from a flat file
Here's a folder which contains many flat files, each file contains address information and all address records in a single file has the same city.
Destination table: Address
SSIS Package Framework Screenshot
1. Create a new SSIS package with a name - CH03_LoadBatchFile.dtsx
2. Create a package variable - FileName, it will be used to save the single file name in a loop.
3. Create a 'Execute SQL Task' with name - 'EST_TruncateCities', edit it .
Configure the connection and SQL Statement.
This task is to truncate address table before loading data.
4. Add a 'Foreach Loop Container' and edit it.
In Collection Pane -
Enumerator - Foreach File Enumerator
Folder - Where the flat file locate
Files - CHO2-Citys-*.txt * means any characters.
In Variable Mappings Pane - Select package variable 'FileName' to map the value in loop.
5. Drag a data flow task, and add below two components in data flow.
Flat File Source - FF_SRC_Address
OLE DB Destination
6. The source is from flat files, so edit 'FF_SRC_Address'.
Browse and choose a source flat file, because the files which match the 'CHO2-Citys-*.txt' have the same file format, so we can choose any matched file first to create mapping columns in this editor.
Configure the properties of each column - please refer to Step by Step 001 - Load data from a flat file
7. Edit 'OLE DB Destination'
Click 'CM_FF_AddressByCity' connection manager, and edit its expression.
9. Save package and run it.
Get all SSIS Step by Step, refer to SIMON'S SSIS Step by Step
Please let me know if you have any questions about this blog.
Contact to me by email - firstname.lastname@example.org
Or Skype - simonlvpin