By default, a data flow task generated by the SSIS Import and Export Wizard extracts data from a single file.
By changing the file connection manager of a data flow task from a FlatFile connection to a MultiFlatFile connection, a data flow task can extract all the designated files in the folder containing the original file using a wildcard in place of part of the original file name.
To run the import/export wizard from an SSIS project in Business Intelligence Development Studio, right-click on the "SSIS Packages" node and select "SSIS Import and Export Wizard."
In the first screen, choose the "Flat File Source" data source and browse to the file you wish to import, then click the "Next" button.
On the following screen click on the "Advanced" node followed by the "Suggest Types" and "OK" buttons to allow the wizard to generate appropriate data types for the fields in the file.
Click "Next," then enter the server name and the database containing the table where you wish to store the data.
In the next screen you choose the table where the data will be stored.
The following screen shows how the data will be mapped from the source file to the destination table.
Click "Next," then the "Finish" button on the following screen to complete the creation of the package.
The resulting package contains a Data Flow Task.
Execution of this package results in data being extracted from the source file and inserted into the destination table.
To reconfigure this data flow task to extract multiple files, right-click on the background of the "Connection Managers" panel and select "New Connection" from the menu options.
Choose "MULTIFLATFILE" from the connection manager type list and click the "Add" button.
Enter "MultiFlatFileConnection" into the "Connection manager name" field and browse to the same file that was used to create the SSIS package, then click the "OK" button.
There are now two file connection managers in the SSIS packages.
Delete the "SourceConnectionFlatFile" connection manager from the package by right-clicking on it and selecting "Delete" from the menu options.
Now there is only one file connection manager in the package.
Click on the "Data Flow" tab of the package editor to display the data flow components.
Double-click on the "Source - VendorSalesData-00041-20050228_csv" component to display the "Flat File Source Editor."
Since there is only one flat file connection manager left in the package, it has already been selected.
Click on the "Columns" node to configure field-mapping.
Click on the "Error Output" mode to configure error mapping, then click on the OK button.
At this point the SSIS package will work, but it is still configured to extract a single file.
To add a wild card to the file name, double-click on the "MultiFlatFileConnection" connection manager.
Change the value of the "File names" field to "C:\dir1\VendorSalesData*.csv" and click the "OK" button.
The package will now extract all data files in the "C:\dir1\" subdirectory with names of the form "VendorSalesData*.csv."
Package Demo
The attached "MultipleFlatFileExtraction.zip" file contains the SSIS package "MultipleFlatFileExtraction.dtsx" that uses a MULTIFLATFILE connection manager. It is an SSIS 2005 package that will import into SSIS 2008 and SSIS 2008R2 without difficulty. The zip file also contains the SQL script "SalesData.sql" that can be used to create the destination table for the SSIS package, and a folder containing 20 data files for use with the package.
To run the demo SSIS package, create the destination table by running the "SalesData.sql" script and copy the 20 data files into a directory named "C:\dir1\." Open the SSIS package "MultipleFlatFileExtraction.dtsx" in Business Intelligence Development Studio and configure the database connection appropriately.
