I have a request of how to write a Execute SQL task in SSIS. More of a fail safe for when a vendor's file does not make it to our system. This is causing the below issue.
I have a SQL job that pulls using a WINSCP Windows task script to retrieve the latest CSV file from a SFTP site. Then the SQL job Step 2 sends the file to the SSIS package for loading into SQL. The problem occurs when the SFTP site does not receive a new file.
Step 1 does not fail because it successfully retrieved the latest file, however, Step 2 using the package fails because the file that was pulled already exists from the previous pull. I need a way to check in the SSIS package to see if the dynamic named CSV file exists before processing. I am very green to writing variables and I have never used the Script task command. Is there a way to do this check before my package kicks off the For each file task by using the Execute SQL task ?
As a side note, I do not have permissions to delete older files off of the SFTP site. And, even though the SQL Job Step 2 says it failed, it still executes the CSV file so now I have duplicate records for the same day.