All,

I'm developing a SSIS package to check for existence of files inside a bunch of known directory paths.

I have a source table with 3 columns.

Table A
Directory path
File Name
FileNotFoundFlag.

My plan is to achieve this in 2 steps.

1) Since i have the directory path, I would like to pull all the filenames within the directory along with extensions and get an output dataset from for each loop.

2) Then with that output, join with the source to update the FileNotFoundFlag to 'Y'.

I would like to achieve this (preferrably without script task). 

Issue Faced: I created execute SQL task which has select distinct directory from table A, -->Assigned to Full resultset with Resultname to 0 and Variablename as "FilePath resultset" (Object). --Successful

I passed on the connection to ForEachLoop File enumator --> expression -->Directory is not accepting the "Filepath result set" variable. Any idea how to pass the directory names and extract all filenames as output?

I'm open for your suggestions if you have a better flow.

Thank you very much.

How about one of these solutions?

http://bidn.com/blogs/DevinKnight/ssis/76/does-file-exist-check-in-ssis

http://sqlmag.com/sql-server-integration-services/simple-effective-way-tell-whether-file-exists-using-ssis-package

Check this out

Load File's information to SQL Server Table

if file exists 

http://www.techbrothersit.com/2013/07/ssis-how-to-check-if-file-exists-in.html?google_comment_id=z13wcjxi5ounh33s223ugdoi3s2juh4mc

Good luck!

Ok I got it to some extent. I used "For Each loop" within a "For Each Loop". 1st Loop was set to ADO Enumerator which dispensed 1 directory path at a time (I have more than 5000 directories). The 2nd inner loop was set to FILE Enumerator. Directory path was mapped to the variable from the the first iteration of the first loop. Now the inner loop picked up all the files within that specific directory and populated the filenames into a variable.

Now I'm wondering how to collect all these variables into a SQL or usable table like a temporary file or a table. (I did not want to unnecessarily create a physical table in the DB for this).

Will let you know if I progress. If anyone has any idea how to create a data set out of the result of For Each Loop, please post your comments.