Importing multiple file types into SSIS / mapping fields

Question

I am working on a new Datawarehouse trying to import a number of different format files from a number of different providers.

The filenames may be the same each month, such as MonthlyReturns.xls/.csv, or a pattern, such as NorthWestSalesData20100101.csv).

We can't ask the providers to change their naming convention.

Do we have to create an SSIS package to import every kind of file-type from every provider, or is there a way we can create a mapping to relate the incoming fields (assuming they have header columns) to the fields in our Datawarehouse ?

The favoured solution is likely to adopt SSIS, although is doesn't necessarily have to if there's a neat and elegant way to cut down on all the administrivia needed to maintain the solution after I'm gone.

I'm actively working on a solution for this problem myself, and will post my adopted solution here, but I wanted to throw it out to the community to get a sanity-check on my question.

thanks in advance for all your great responses.

Answer 1

You can create a for each container to iterate over the files. The filename is then assigned to a variable. This way you can work with any file without having to know its name beforehand. The for each container can be filtered based on file extension(s).

You can then create a mapping facility based on the file name, file extension, file format, or column headers.

An example of using a for each container can be found here.