Many times we require to import multiple files in SSIS. There are various ways to achieve this and the best option depends upon on case to case. The available options are:

  1. For Each Loop and a variable to create dynamic connection
  2. In Single dataflow, use separate Connection for different files and use “Union All” or separate Data flow for each file
  3. Use “MultipleFlatFile” connection
  4. For Each Loop and use ExecuteSQL to have OPENROWSET to read data from flat files.

Option 1 and 3 requires having same file structure but option 2 and 4 supports different file structure.

The following section describes Option 1 i.e. how to use For each loop and a variable to create dynamic connection and load multiple files.
Step 1:  Create a variable “FileName” and another variable “FeedImportPath”. Assign default value to these variables.
Step 2: Place a “For Each” task  in Control Flow and specify the “Directory” as @FeedImportPath

1_SQL_Server_Import_Multiple_Files_in_SSIS

Step 3: In Variable Mapping, set the variable “FileName” with index 0

2_SQL_Server_Import_Multiple_Files_in_SSIS

Step 4: Create a FlatFile Connection and write expression for Connection String

3_SQL_Server_Import_Multiple_Files_in_SSIS

Now you are all set to use ConnSourceFile to read multiple files, (only if used within ForEach task).

4_SQL_Server_Import_Multiple_Files_in_SSIS

In my next post, I will illustrate how to use “MultiFlatFile” Connection to read multiple file.

 

Regards

Rakesh Mishra

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook