Import Multiple Files in SSIS

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

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

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

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

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 Twitter |  Follow me on FaceBook