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”