In one of my package I have to transfer file from Secure FTP site (SFTP). I am using WinSCP for Transfering file from SFTP site to local directory.that works fine I don't have any problem in transffering file. But before transfering file I need to check weather file is there or not. Is it possible check file existance on SFTP site with SSIS before downloading it to local directory. Thanks, Kishan

No. The only way you can interact with SFTP in SSIS is through third party tools like you have. There are others that may provide more functionality. Search the SSIS Community Tasks and Components site for SFTP for some options. What happens now when the file you are attempting to download does not exist? The Execute Process Task fails, correct? You can handle this failure using Precedence Constraints to handle it as you want to. You can even use the return value - which may specifically indicate that the file did not exist - or the ErrorOutput to determine what went wrong. Talk to me now on

Thanks for your suggestion. I already checked SSIS Community Tasks and Components and suggested to my client but they don't want to spend $$$. Actually I need to download multiple files. So I am using "*.txt" for selecting files. If there is no files in source directory , task execute without any error but there won't be any file in destination directory as nothing is in Source.

Then simply use a script to detect if your download directory is empty... Talk to me now on

Right now I have created one more directory where I am downloading new files. and then moving then to another directory. Because Destination directory already has some previously downloaded files. But I was looking for some easy solution where I don't have to create another directory just to check files existance.

Unfortunately, I don't think you'll find one. All the SSIS/SFTP tools I know of are commercial. Talk to me now on

Thats fine. I will continue searching on this. Thanks a Lot for your help.

Todd, I'm trying to find a solution to this problem but without success. Can you please explain a little bit more in detail what you mean by "You can handle this failure using Precedence Constraints to handle it as you want to. You can even use the return value - which may specifically indicate that the file did not exist - or the ErrorOutput to determine what went wrong."? The problem for me is that the scheduled package fills the history log with this error, which I of course don't want.

If you happen to use a third-party command-line tool (or are considering one), they usually report different values as a "return code" from their execution, depending on what went wrong. The Execute Process Task in SSIS can capture that return code into an SSIS variable. Zero means success (usually). You can then use that variable in precedence constraint expressions to control what task(s) get executed next. For example, you could send a mail with the error code if it's nonzero, or continue execution if it is zero. Talk to me now on