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...