Easy way to check ftp for files before running SSIS package

0
votes

I have an SSIS package that I am planning on running every 15 minutes. This package will take the files from the ftp server and moves them to a folder on the server and then processes the files.

What I need to do is check to see if a file even exists before I run the SSIS package.

I am doing a similar thing with the files in the folders within the server to move them to the ftp server to go back to the client.

Any one have some experience with this?

1
file-iossis
You can create a batch file to check if the files are present in ftp else use a script task in SSIS to check for files. If files are present then proceed with package execution else abort – praveen
Why do you want to check if a file exists first? You can just run the package, and if the FTP download step in the package finds no files then exit the package. Of course this assumes that the FTP download is the first step, but that seems to make sense here anyway. – Pondlife

1 Answers

1
votes

If it's a remote FTP server that you wish to check for files, and your FTP task is in the SSIS package ... then you won't be able to check for files without running the SSIS package. If you check for files in an initial FTP task within the package, then you're firing up an SSIS package every 15 minutes, which probably isn't a good practise.

The next thing that happens is that the business asks why it can't run every five minutes, and then they ask why it can't run every two minutes. It's a lot of server overhead.

On occasion I have used two SSIS packages, one to perform the FTP transfer, and the other to do local processing, and set these up as two separate steps of a SQL Server Agent job. The reason for that was to separate the error behavior. But it was for a nightly job, not something that ran every 15 minutes.

If you're checking for files on the local network, or using FTP directly, then you could use a batch file or something as the first step of a SQL Server Agent job (and abort if necessary). That would keep the overhead lower.