Solved

SP /Trigger -  File Search to begin jobs.

Posted on 2001-06-18
3
233 Views
Last Modified: 2008-03-06
We are using FTP to transfer a number of files from another machine(the type and details are not important) to SQL Server.  
At the end of the nightly process I will receive a Text file called READY.txt, which will let me know that the process has completed from that server.

I would like to create a sp that checks every five minutes for that file.  I understand the job schedule portion.  

THE QUESTION:  How do I create either a DTS package or a sp to search for this file to start my evening's / morning's processes?  I am looking for something that states:  
          If C:/FTP/READY.txt exists
          Then go to the next step
          Else keep waiting until my time is up.

Thanks.

         
0
Comment
Question by:brine
  • 2
3 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6203070
something like this in an sp scheduled every 5 mins.

create table #a (s varchar(1000))
insert #a
xp_cmdshell 'dir /B C:/FTP/READY.txt'

if exists (select * from #a where s like ...)

look at the result you get in #a for the file - you might want to check the file date too.
0
 
LVL 18

Accepted Solution

by:
nigelrivett earned 200 total points
ID: 6203178
more like

create table #a (s varchar(1000))
insert #a
exec master..xp_cmdshell 'dir C:/FTP/READY.txt'

if exists (select * from #a where s like '%'READY.txt%')


(I do a like instead of /B with = because you will probably want to look at other attributes too).



0
 

Author Comment

by:brine
ID: 6207669
Thanks ... Sorry I took so long to respond, but I needed to make time to test.  
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question