I have the need to search a directory containing 1000+ files on remote servers where I only have access to SQL Server Management Studio and Explorer. I have written the below SQL statement which does what I need, only incredibly slowly. I fear using this many temp tables and the cursor is the main issue. Any ideas on how I can speed this up?
EDIT: I forgot to mention that I need to keep this query based. I will not necessarily have full permissions regarding jobs and/or SSIS packages.
--Drop temp table if it already exists
IF OBJECT_ID('tempdb..#tempFileContents') IS NOT NULL
DROP TABLE #tempFileContents
DECLARE @FindText AS VARCHAR(255)
DECLARE @FileDir AS VARCHAR(255)
--Directory to search
SET @FileDir = 'C:\Users\*********\Desktop\TEMP\'
--Text to search
SET @FindText = '1234'
----------------------
----------------------
--Crete temp table to store output lines from text file
CREATE TABLE #tempFileContents
(
lineText TEXT
)
--Create declared table to store found file names in the directory
DECLARE @files table
(
filename VARCHAR(255)
,depth INTEGER
,files BIT
)
--Create declared table to store the text the string ws found in along with the filpath of the file.
DECLARE @foundText table
(
LineNum INT
,foundText TEXT
,filename VARCHAR(255)
)
--Create table of files in the directory
INSERT INTO @files
EXEC xp_dirtree @FileDir, 10, 1
--Update file names to include filepath
UPDATE @files SET filename = @FileDir + filename
--Varchar used to store the derived SQL string
DECLARE @sql NVARCHAR(1000);
--Create cursor for file searching
DECLARE @text VARCHAR(255)
DECLARE @ID VARCHAR(255)
DECLARE IDs CURSOR LOCAL FOR select filename from @files
--Open cursor
OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
--Truncate temp table prior to searching a file
TRUNCATE TABLE #tempFileContents
SET @sql = 'BULK INSERT #tempFileContents FROM "' + @ID + '"'
EXEC(@sql);
--Insert into results table
INSERT INTO @foundText
(LineNum, foundText, filename)
SELECT ROW_NUMBER() OVER( ORDER BY @ID ) AS 'rownumber', lineText, @ID FROM #tempFileContents
DELETE FROM @foundText
WHERE foundText NOT LIKE '%' + @FindText + '%'
OR foundText IS NULL
FETCH NEXT FROM IDs into @ID
END
--Close off and dispose of unneeded resources
CLOSE IDs
DEALLOCATE IDs
DROP TABLE #tempFileContents
--Display results
SELECT * FROM @foundText