How can we check if a directory is valid or not inside for each loop . I am having multiple files at some location and i want to load that file but before this i want to check if the directory is correct or not . If the directory is not correct it will throw error for wrong directory.

I am passing Directory path in config file.

to avoid this situation do i need to write script task for this

  • yes, you need to add a script task to do this for you. Commented May 23, 2019 at 18:57
  • Thanks Jonathan , Can you please tell me that if we can use Script task to check the directory outside the For each loop and if it is true in that case only for each loop will trigger. if we check directory inside the loop then it will run the script task every time when we have more than one file. Commented May 27, 2019 at 10:42
  • Your best bet is to have the script task check the directories for you, then have it fill an ADO recordset variable, then have the for-each loop over the returned result set. Commented May 28, 2019 at 12:58

1 Answer 1


if you have the right amount of permissions you can use something like the function below to check if a file exists.

I have not tried to check if a folder exists though.


create FUNCTION dbo.fn_FileExists(@path varchar(8000))
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)

this is an example as how to call the above function:

-- get the latest Full backup still on Disk

  a.backup_set_id AS [LATEST Full backup still on Disk]
  ,compressed_backup_size= a.compressed_backup_size
  ,[File Exists]= CASE WHEN master.dbo.fn_FileExists(b.physical_device_name) = 1 THEN 'Yes' ELSE 'No' END
  FROM msdb.dbo.backupset a
  INNER JOIN msdb.dbo.backupmediafamily b
         ON a.media_set_id = b.media_set_id

  WHERE 1=1 
  and a.backup_set_id = (
                            SELECT MAX(f.backup_set_id) 
                                FROM msdb.dbo.backupset f 
                          INNER JOIN msdb.dbo.backupmediafamily b
                                  ON f.media_set_id = b.media_set_id
                               WHERE 1=1
                                 AND f.[type] = 'D'
                                 AND f.DATABASE_NAME='Junoreporting'
                                 AND master.dbo.fn_FileExists(b.physical_device_name) = 1 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.