Just a slight amend to SSCrazy's script and worth a bump as it was very useful. If found that it didn't look in a particular folder as the @folder variable was only defined and never used. It simply required getting rid of that and using the full path in the @Command variable. The updated script is:

SET NOCOUNT ON

DECLARE @Command VARCHAR(1000)

SET @Command = 'dir d:\ftp\clients\somecompany\in /b /s ' -- the full path on the SQL Server instance

DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))

INSERT INTO @FilesInAFolder

EXEC MASTER..xp_cmdshell @Command

; WITH CTE AS

(

SELECT REVERSE(FileNamesWithFolder) ReverseFileNames FROM @FilesInAFolder

)

SELECT FileNames = REVERSE ( LEFT (ReverseFileNames, CHARINDEX ('\', ReverseFileNames)-1))

FROM CTE

WHERE ReverseFileNames IS NOT NULL