SSIS Script Task

Question

I have a table which has the list of files in a column

Column values are File1.csv, File2.csv etc.

Now in SSIS I would like to retrieve the list of files from the above table and then check if each file is present in a directory.

If a file is not present than I would like to return a flag so that further package load doesn't continue

Please advice how that can be done

Answer 1

Hi John,

We can use Foreach Loop Container to read the file name and then use Script Task to check if the file exists.

Please refer to the following links:

Looping Through a Result Set with the ForEach Loop

[SSIS -How to Check IF File Exists In Folder Script Task

Best Regards,
Mona

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @John K ,

I would do it differently.
Run the following T-SQL. And LEFT JOIN the @Bluemchen table with your real table that is holding file list. That would be the check if files enlisted in the table exist.
Call it from SSIS Execute SQL Task.

    DECLARE @tbl TABLE (id INT IDENTITY(1,1) PRIMARY KEY  
          , [fileName] VARCHAR(512)  
          , depth INT  
          , isfile BIT  
         );  
      
    DECLARE @folder VARCHAR(256) = 'e:\temp\xls'  
      
    INSERT INTO @tbl ([fileName], depth, isfile)  
    EXEC master.sys.xp_dirtree @folder,1,1;  
      
    -- test  
    SELECT * FROM @tbl;  
  
-- filter out not need files  
SELECT *  
   , FullyQualifiedFileName = CONCAT(@folder, '\', [fileName])  
FROM @tbl  
WHERE isfile = 1  
   AND [fileName] LIKE '%.csv';  

Answer 3

In a Script task - you can use Split to split the column values, then check if the files exists. You probably need to include the path to the file somewhere, I would set either a package variable or project parameter for that.

Setup a variable in the package to hold the return value from the script task as a boolean. Set to true or false in the script task as needed and then set a precedence constraint to branch.