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
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.
USE MASTER
GO
create FUNCTION dbo.fn_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT
RETURN cast(@result as bit)
END;
GO
this is an example as how to call the above function:
--=============================================
-- get the latest Full backup still on Disk
--=============================================
select
a.backup_set_id AS [LATEST Full backup still on Disk]
,a.media_set_id
,a.backup_start_date
,a.backup_finish_date
,a.type
,backup_size=a.backup_size/1024.00
,a.is_copy_only
,compressed_backup_size= a.compressed_backup_size
--,b.media_set_id
,b.physical_device_name
,[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
)
