ssis Check if a file exists Steps to check if a file exist or not

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

To complete this objective following tasks are required.

  1. Foreach Loop Container: To iterate over a user configured directory for files.
  2. Expression Task: To update a variable if file exists.

Steps

  1. First goto Solution Explorer double click on Project.params and create a parameter FolderPath of type string, put value like E:\DataDir\SourceFiles.

  2. Create user variables FileNameFromFolder (String), FileToSearch (String) assign value that you want to check and create a variable IsFound (Boolean).

  3. Drag and drop a Foreach Loop Container from the SSIS Toolbox under Containers section.

  4. Double click on the Foreach Loop Container on the left hand side of Foreach Loop Editor click on the Collection. On the right side set Enumerator as Foreach File Enumerator, now for the Expression click on the three dots which will open a Property Expression Editor. Select Directory as property and for expression select @[$Project::FolderPath]. Click OK. Foreach Loop Editor Expression Property

  5. Now in Foreach Loop Editor for the value of Files set *.txt, for the value of Retrieve file name select Name only, normally we select Fully Qualified as it returns file name with the complete path. Check the Traverse subfolders if there can be more than one folder inside a folder. Foreach properties

  6. On the left select Variable Mappings, on the right side select User::FileNameFromFolder which will automatically get Index as 0. The file names from the FolderPath will be assigned one by one to the FileNameFromFolder variable. Click OK. Foreach Loop Variable Mappings

  7. Drag and drop a Expression Task inside the Foreach Loop Container from the SSIS toolbox present under the section Common.

  8. Double click on the Expression Task, in the Expression Builder write following code. Click OK.

    @[User::IsFound] = @[User::FileNameFromFolder] == @[User::FileToSearch] ? TRUE : FALSE enter image description here

  9. The Code above compares the file name that we want to check with the file name from the folder, if both are matched it sets IsFound to True (File Exists).

  10. Now the value of IsFound can be used with precedence constraint according to need.



Got any ssis Question?