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

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.

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.

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.

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

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.