How to monitor flat file for changes then run a SQL import job I have a file that is a flat text file that needs to be imported into my SQL Server every day.  The problem is that I have to wait for someone else to download and place the file before I can import it.  The download process is from within a proprietary application and not from a website.  How can I automate the process by monitoring for the file change and then starting import job?
Below is a SQL Table-Valued Function that I got from Jeff Moden, that will return the File Attributes of a single file:

/*
    Return File Attributes about a single file.

WARNING: Uses the sp_OACreate method, which should be SA use only.

(Note: much of this is copied from Jeff Moden's routines)

TEST:
  SELECT * FROM pps.fnFileInfo('C:\install.exe')

*/
ALTER FUNCTION [pps].[fnFileInfo]( @FileName As VARCHAR(255) )
RETURNS @FileInfo TABLE
(
    RowNum           INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Name             VARCHAR(128), --File Name and Extension
    Path             VARCHAR(128), --Full path including file name
    ShortName        VARCHAR(12),  --8.3 file name
    ShortPath        VARCHAR(100), --8.3 full path including file name
    DateCreated      DATETIME,     --Date file was created
    DateLastAccessed DATETIME,     --Date file was last read
    DateLastModified DATETIME,     --Date file was last written to
    Attributes       INT,          --Read only, Compressed, Archived
    ArchiveBit       AS CASE WHEN Attributes&amp;  32=32   THEN 1 ELSE 0 END,
    CompressedBit    AS CASE WHEN Attributes&amp;2048=2048 THEN 1 ELSE 0 END,
    ReadOnlyBit      AS CASE WHEN Attributes&amp;   1=1    THEN 1 ELSE 0 END,
    Size             INT,          --File size in bytes
    Type             VARCHAR(100)  --Long Windows file type (eg.'Text Document',etc)
) As
BEGIN
    Declare @CurrentName As Varchar(255); SET @CurrentName = @FileName;
    DECLARE @ObjFile          INT          --File object
    DECLARE @ObjFileSystem    INT          --File System Object  

    --===== Create a file system object and remember the "handle"
    EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT

    --===== These variable names match the sp_OAGetProperty options
    DECLARE @Attributes       INT          --Read only, Hidden, Archived, etc, as a bit map
    DECLARE @DateCreated      DATETIME     --Date file was created
    DECLARE @DateLastAccessed DATETIME     --Date file was last read (accessed)
    DECLARE @DateLastModified DATETIME     --Date file was last written to
    DECLARE @Name             VARCHAR(128) --File Name and Extension
    DECLARE @Path             VARCHAR(128) --Full path including file name
    DECLARE @ShortName        VARCHAR(12)  --8.3 file name
    DECLARE @ShortPath        VARCHAR(100) --8.3 full path including file name
    DECLARE @Size             INT          --File size in bytes
    DECLARE @Type             VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)

    --===== Create an object for the path/file and remember the "handle"
    EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName

    --===== Get the all the required attributes for the file itself
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Path',             @Path             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath',        @ShortPath        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Name',             @Name             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName',        @ShortName        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated',      @DateCreated      OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes',       @Attributes       OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Size',             @Size             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Type',             @Type             OUT

    --===== Insert the file details into the return table        
    INSERT INTO @FileInfo
           (Path, ShortPath, Name, ShortName, DateCreated, 
            DateLastAccessed, DateLastModified, Attributes, Size, Type)
    SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated, 
        @DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type

    --===== House keeping, destroy and drop the file objects to keep memory leaks from happening
    EXEC sp_OADestroy @ObjFileSystem
    EXEC sp_OADestroy @ObjFile

    RETURN
END


The way I would use this is to make a table with a single column and record to keep track of the DateLastModified of your import file.  When that changes, run your import then, if successful, update the time value in the table.



Starting an Agent Job from SQL and monitoring its progress and result is actually something of a headache.  It would probably be easier to modify your job as follows:


Add a new step before the existing import step[s] that retrieves the current DateLastModified value from the file, compares it to the saved value in the table, and if the same (or earlier) exits with an error.  Then set the Job Step to "Exit With Success" in the event of an error.  If the date is greater (i.e., an updated import file) then the proc should exit normally and the Job should proceed to the next (import) step(s).
Add another step at the end of the job that reads the DateLastModified value again and updates it to the table.
Finally set the whole Job to run on some reasonable interval, such as every 5 minutes (or alternatively, define the first step to retry every 5 minutes).