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

• Jonathan Fite: yes, you need to add a script task to do this for you.
• deep kachhawa: 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.
• Jonathan Fite: 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.

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 
)