MSP_CHECKFILEEXISTS

-- Check for File and execute Job
-- Jbabington@hotmail.com
-- Set file and path
-- Used to force Error on Job Package
-- Notes: 
--Xp_fileexists (file path information) Settable
--DROP PROC MSP_CheckFile
-- Can be altered for simple variable

CREATE PROC MSP_CheckFile --@filename VARCHAR(100)
AS
SET NOCOUNT ON
DECLARE @filenameVARCHAR (100),
@fileexists INT
--EXEC master..xp_fileexist '<Path>\+@filename+>'
EXEC master..xp_fileexist '<PATH\filename.txt>'
,@fileexists OUT
IF @fileexists = 1          -- The file exists
   EXEC msdb.dbo.sp_start_job @job_name = 'Insert Job Name'
ELSE 
-- * Check if Fileexists val = 1
IF @fileexists = 0 
BEGIN
Raiserror('Invalid File value',16,1) with seterror
Return(@@Error)
END