Automate import of CSV files in SQL Server

Question

I'm currently using SSIS to import a whole slew of CSV files into our system on a regular basis. These import processes are scheduled using the SQL Server Agent - which should have a happy ending. However, one of our vendors from which we're receiving data likes to change up the file format every now and then (feels like twice a month) and it is a royal pain to implement these changes in SSIS.

Is there a less painful way for me to get these imported into SQL Server? My requirements are fairly simple:

1. The file formats are CSV, they're delimited with commas, and are text qualified with double quotes.
2. The file name will indicate into which table I need these imported
3. It needs to be something which can be automated
4. Changes in file format should not be that much of a pain
5. If something does go wrong, I need to be able to know what it was - logging of some sort

Answer 1

BULK INSERT is another option you can choose. You can define your own templets of the file with it:

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql

https://jamesmccaffrey.wordpress.com/2010/06/21/using-sql-bulk-insert-with-a-format-file/