Please, Redgate, do something to address the spam soon. Removing hundreds of garbage notifications does nothing for my mood.

Your INTERSECT query is comparing all of the columns whereas the JOIN version compares only right(SSN,4) ... explaining why the second returns more matches.

If the error happens only when you run the package from SQL Agent, it's probably a permissions issue. Does the SQL Agent user have rights to query AD?

Your C# skills could do with some work! 🙂

Try this:

public void Main()
{
string networkPath = Dts.Variables["User::FileDestinationPath"].Value;
string[] filePaths = Directory.GetFiles(networkPath , "*.csv", System.IO.SearchOption.AllDirectories);
foreach...

For those who would like to try for themselves, a Regex tester can be found here. It suggests that Frederico's concern is not an issue, because I tested...

SQL Prompt has some very useful options for handling brackets (remove them unless doing so causes an issue) and aliasing: