In my previous post, I have discussed about various options available in SSIS to load multiple flat files. One of the mentioned options is using “MultiFlatFile”. A multi flat file connection allows us to access multiple files in parallel i.e. we can read data from multiple files in parallel and it produces data as UNION ALL of all files as if we are reading data from a single file.
To use multiple flat files in a connection, we need to create a connection of “MultiFlatFile” connection type. Following are steps to get this done:
Step 1: Right click on Connection tab and select “New Connection”
Step 2: Select “MultiFaltFile” in available connection manager type window.
Step 3: Give name to Connection Manager and choose the files that you want to process. You can use wildcard character to select all files in the folder. We can have files from multiple directories. Each file (fully qualified name)is separated by Pipe “|”.
By default, connection manager assumes that column name does not exist in the file and it generated column name for you. If files contains column name as 1st data row then set the properties accordingly. In Advance tab, we can change the column properties like name and data type.
Now you are done with configuring the connection manager to use multiple file to be processed in parallel.
Create a data flow task and use “Flat File Source” and specify the newly created “MultiFlatFile” connection as source.
Points to remember: “All the files that are going to be processed using MultiFlatFile MUST have the same format including data types”
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
Problem: I will be getting flat files in a folder.there can be one or multiple file.SSIS should pick the data from the folder and upload to sql db.all the files will have same fields and datatypes.Table will have same fields and datatypes.in which way ssis will get the intimation that a new file is there and it has to pick the filefrom folder?
One of the options is; use a DFT (inside a ForEach control) to read a file and load that your table.
Let me know if you need a sample.
-Rakesh
Another option is to use MultiFlatFile with wildcard.
Hi Rakesh,
Do you have a sample for using the MultiFile connection instead of the MultiFlatFile?
Thanks,
Jonathan
Rakesh,
What if we want to load data from different flat files i.e, if First flat file has 10 columns and Second Flat file has 20 columns?
The two files columns have different data types.
Can we use multiflatfile connection manager for this scenario?
thanks Rakesh. helped me resolve need to combine multiple flat files in ssis for processing.