SSIS Script Task

John K 186 Reputation points
2020-09-03T03:56:20.857+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,891 Reputation points
    2020-09-03T07:18:23.597+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,831 Reputation points
    2020-09-03T04:19:45.53+00:00

    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';  
    

  2. Jeffrey Williams 1,886 Reputation points
    2020-09-03T20:12:36.487+00:00

    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.

    0 comments No comments