CSV to SQL Automated import

We get on a daily basis a csv file from our vendor containing product updates. It is delivered via ftp as a zip folder. In the zip is the csv file. I need some script that does the following to import/update into our SQL Server tables. SQL Server is 2012 Unzip original file Import/Update products from the table Confirm successfully updated Move zip file to backup folder so that when the new file is available the script is run against that one. Now it could be that the script will need to cross reference Product categories as well as they may not be the same as in the csv. E.g. In the csv file the product Major Category could be Small Business but in the SQL db the category would be SMB. So cross reference capabilities will be required.