Today at work we needed to compare the contents of a SQL table against an FTP directory.  We originally had a process that involved a lot of steps using Excel, NotePad, and command line FTP.

You could create a PowerShell script that iterates the SQL Server data and verifies each file is on the FTP server. This seemed like it would be slow so I opted for running FTP directly from my query (assuming you have xp_cmdshell enabled). Here's an example. Notice it's getting the FTP commands to run from the ftpoptions.txt file.

declare @FilesOnDisk table

(

[File] [nvarchar] (50)

)

insert into @FilesOnDisk ([File]) exec xp_cmdshell 'ftp -A -s:c:\temp\ftpoptions.txt ftp.microsoft.com'

select * from @FilesOnDisk where [File] is not null

Now we can join this temp table data to our local SQL Server table to find missing files on the FTP server...