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)