September 15, 2014 at 5:00 pm
Someone has probably asked this before but I'm not even sure how to search for it. The SSRS server is SSRS 2008 R2 Enterprise.
Background information:
Every month another company will place a text file in a specific FTP location. We have a process set up to watch for that file. When the file arrives, that process imports it into an Oracle database and then archives the file into another folder. Upon completion of the file import, that process will execute a SQL Server (2008 R2) stored procedure to start a SQL Server agent job. That job already contains steps to run the jobs belonging to three specific data-driven subscriptions (it's a long story) for three reports. This is how we set up "automatic system generated reports" upon import of a file.
The three reports each have a data-driven subscription that will generate the report in PDF and/or Excel and write it to a specified folder on a server. The job that runs all this will then execute a stored procedure to email the users that the reports were run and gives them the location of the PDFs and Excel files.
The problem we're now running into is that some of those files aren't written for about 20 minutes. So the emails went out 20 minutes before the files were ready. Our current (easiest) solution is to run the import process after hours only. 🙂 Any ideas on how to fix this problem? Hopefully easily?
Thanks!
Rebecca
September 15, 2014 at 5:14 pm
The easiest thing to do would be to create another file system watcher similar to the one that you already have watching for the FTP file and then when the file appears simply send the user and email that the file is there and remove the email part from the procedure.
A bad thing to do but would work is simply in the procedure that sends the email do a loop that checks for if the file exists and if it doesn't sleep the thread for sometime and then check again. If it finds the file then email the user and exit the infinite loop.
September 15, 2014 at 7:12 pm
Thanks! One of my co-workers suggested the watcher process as well. So we may do that. Thanks! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply