SSIS Help: Archive a file from the list of files that does not exist in SQL Table

Question

I would like to create this in SSIS package, wherein, it will loop in the list of files in a directory or folder and check each files if it exist in a SQL queried table. If the filename does not exist in the table, it will be archive in a different folder.

Thanks is advance for any help! =)

Answer 1

You could use a For each Loop container that can be pointed to the said directory or folder and then store the file name (Name and Extension, Fully Qualified or Name only depending on the requirement) in a variable eg. LoopVariable. Use a Execute SQL task inside the container that has a prepared statement like - If exists (select * from [Files] where [FileName] = ?)Select 'Yes' Else Select 'No' - In the Parameter pass the LoopVariable and Result map to another variable FileExists. In the precedence Constraint editor use - expression - [User::FileExists] =="No" and connect to a file system task that moves the file to the archive folder.
Note: You might need to form the file path in case you are not getting the fully qualified name from the For loop container.