How do I import one sheet of multiple excel files with dynamic meta data into one SQL table

See more:

C#

SSIS

I am having an urgent issue. I couldn't find any answers for this specific scenario:

Import multiple Excel files, with multiple sheets, and different meta data into one SQL table. The first sheet of each Excel file (6000 files in total) has to be imported into a SQL table. There will only be one table for all the first sheets of these files together.

We found out that we have to use C# (script task in SSIS).

The meta data differs between the files. Below the column names of a few files to get an idea: - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010; - Balans (geconsolideerd) '07->'08 31-12-2008 31-12-2007 31-12-2006 - Balans (geconsolideerd) '13->'14 31-12-2014 31-12-2013 31-12-2012 31-12-2011 31-12-2010 31-12-2009 31-12-2008 31-12-2007 31-12-2006 31-12-2005 31-12-2004 31-12-2003

At the end, we are only interested in 'Balans (geconsolideerd)' and data of 5 years (current year - 2 and the 4 years before). So, right now 2014 - 2010. The column with the -> sign should be dropped, even as the year columns which are out of the range, but this will be step 2.

The first step is to get all the data of the first sheet of all the 6000 files into a data table/ temporary table.

Can you help me? If you need more information or if I need to clarify things, please let me know as well.

Thanks in advance.

Chelsea Disseldorp

What I have tried:

A lot of things already, unfortunately.