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.
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
3 Comments on “Import Multiple Files in SSIS”
I need option 4 :
For Each Loop and use ExecuteSQL to have OPENROWSET to read data from flat files.
for support diiferent file structure… for this i need full explained example can u make it please.
could you help me with procedure for dynamically importing multiple flat files with different formats into multiple tables of database through SSIS
You have not described option 4?