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