Directory Verification with For Each Loop in SSIS

Question

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

• Unexpected behavior of Foreach Loop in SSIS 2012

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.

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 
)