Hi, I need to create a sql job on one server which looks for a file present in another server. Both are sql servers. If that file is not present then the job should fail but it needs to re-start after 10 min again and look for that file. This needs to be done continously for 2 hours. Once it finds the file it should stop being successful. Please advice. Thank you. Shiv

Hi, I need to create a sql job on one server which looks for a file present in another server. Both are sql servers. If that file is not present then the job should fail but it needs to re-start after 10 min again and look for that file. This needs to be done continously for 2 hours.I want to create an SSIS package for this functionality and call it from a job. Once it finds the file it should stop being successful. Please advice. Thank you. Shiv

File Watcher Task component can do that. I will point you to an example: http://www.sqlis.com/post/File-Watcher-Task.aspx Once the package is created set the SQL Agent to execute it until successful. PS: You will need a Domain Account based proxy to run that Agent job. Arthur My Blog

Hi What kind of file is it? Can you create a powershell script or VBscript to do this task?and run it thorough SQL Agent or Windows Task scheduler. Thanks

Thank you, that really helped. But one question, How will the SSISpackage execute again if if didnt find the file for the first time. I mean , I've set the " Time out " as 10 min, so does that mean it would again start searching for that file after 10 min if the file is not there. Please advice. Thank you. Shiv

Fail the package thereafter and set the Agent job to re-execute the failed step until the time frame is up. Arthur My Blog

Hi Arthur, I have a new requirment so here's the thing, if the ssis package dosent find the file after 2 hrs it shouls send an e-mail. i've included that functionality to send an e-mail and its working fine. but the new requirment is that, the job( or pacakge) should again start searching for the file after sending the mail notification and this procss should continue for every 2 hours untill it finds the file. To summarize, once the mail notification is sent, it should again start searching for the file for every 2 hrs. Please let me know if this functionality can be done in sql sgent job or SSIS pacake which is getting called in that job. Thank you.

Can you make it a new post please so it would help others respond?Arthur My Blog