Solved

SP /Trigger -  File Search to begin jobs.

Posted on 2001-06-18
3
230 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now