Monday, June 25, 2012

SSIS Step by Step 003 - Load multiple flat files in a loop container

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.

Source folder:

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'

 8. Now we need to configure the flat file connection manager, the current flat file connection is tied to a specified file. We need to configure it with a new value which is from loop container - User::FlatFile in step 4.

Click 'CM_FF_AddressByCity' connection manager, and edit its expression.

Map connection string property  to package variable 'FileName'

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 -
Or Skype - simonlvpin

1 comment: