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:
- For Each Loop and a variable to create dynamic connection
- In Single dataflow, use separate Connection for different files and use “Union All” or separate Data flow for each file
- Use “MultipleFlatFile” connection
- 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.