For each file Extract the patient Id from the file name Check whether the extracted patient Id matches a row in the SQL Server lookup table If a match is found move the file to folder B move the file to folder C End If Next file
OK, so you need to use a FOREACH container to iterate round the files in the folder.
Use an Expression to extract the Patient Id from the file name and store in a package-scoped variable.
Use an ExecuteSQL task to check for the value in the lookup table, using the stored variable. Return 1 for found, 0 for not found (for example).
Use File System Tasks or a C# Script Task to copy the files, if 1 was returned (use a Precedence Constraint to control whether this gets executed).
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.