How do i get the newest CSV and import it to sql table using BCP. I receive files based on the date and time, so I need a way to get files that a the latest from the time i imported the last one. Please help.
I think the most elegant solution involves stepping outside of T-SQL and using something like PowerShell or Python to write a routine that will handle this. PowerShell and Python can work closely with the OS to easily get the timestamp of the file itself or to easily parse the file names since you mentione din your comment to Kev that the file name includes the date and time.
If you want to keep it inside of T-SQL, then I agree with Kev that a control table is the best way to go.
Of course, whether you choose PowerShell, Python, T-SQL or something else, remember that after BCP is done with the file you can move it to an archive directory. That could simplify the entire flow.
I think that SSIS would be the best tool to use. You would still have a control table that holds the previous files, and you would put the imported files into an archive folder and delete when necessary.
The you can simply use a for each loop container in the control flow to get the filenames, and a data flow task to import the file, then a file system task to move the file to the archive folder. Seriously, it would only take about 10-15 minutes to setup.
No one has followed this question yet.