Import daily csv with varying available headers I receive a csv daily from an external company capturing feedback for us.
Unhelpfully the csv headings vary. There could be any combination of headers.
For example:
day1: heading1, heading2, heading3, heading4, heading5
day2: heading1, heading2, heading4, heading6
day3: heading1, heading2, heading7, heading8
day4: heading1, heading2, heading3, heading4, heading5
We have 18 months (no one has done anything with this data until now) of backlog files and so far I have found 22 different combinations...
I know what all the potential headings will be so I can have an SQL table that contains all the appropriate columns but I am struggling to handle the variance for import.
Is there a way to handle this in SSIS?
I have looked at bcp and openrowset but I can't seem to make it work.
As this file is received daily I want an sql job that imports them to my table for reporting. I can only create an SSIS package that will import a fixed flat file but I need it to be dynamic. Worst case scenario would be to have 22+ different SSIS packages and run a specific 1 for the given headings available but then I would need a way of automatically reading the structure of the csv to decide which package to use.
I'm lost, so does anyone have any direction?
SQL 2016 (13.0.5622.0)
Many thanks
I would do it like this:

There exists some working table where the data must be placed into. It contains columns for all data which may present in CSVs, with proper names and datatypes.

Create names table with 2 columns: column name used in CSV; according column name in working table. Like


CSV_name    | table_name
------------|-----------
ID          | id
user_id     | id
Employee_id | id
name        | first_name
FirstName   | first_name
...         | ...

Each column is defined as unique.

Create a procedure which:


Loads first row from CSV file (the file is renamed to some pre-defined name or its is got as a parameter), parses it to separate names, then adds them into CSV_name ignoring duplicate errors, then counts the amount of names in names table with NULL in table_name.

If there are CSV_name rows with table_name IS NULL then procedure types a message and breaks. Operator looks names table and fills table_name column with according values, then calls the procedure again.

If all CSV_names have according table_name then procedure creates temporary table and loads the whole data into it. After data loading the procedure builds INSERT INTO query text and copies the data into working table. Alternatively correct importing query text may be created/executed in one step.