I have about 1.8 mln files in a folder. These need to be copied to another folder.
There is a table [staging].[filelist]
that tells me which files to copy.
I have a SQL statement that will get a list of filenames from that table and then copy those files to a destination folder. However, the copy commands are serially executed. It takes a horrible long time, even locally on SSD disks. This SQL Statement is executed by SSIS on a MSSQL 2017.
Ideally I would like to copy those files all at once or parallel, not by copying each file individually. To speed up things.
Is this possible, using SSIS, SQL or Powershell? I do see solutions for copying indidual files (as I do now), I see solutions to copy whole directory's but no solution to 'bulkcopy' groups of files...
Below my code that works but is horribly slow.
DECLARE @SRCFOLDER as nvarchar(256) = 'E:\SRCFOLDER'
DECLARE @DESTFOLDER as nvarchar(256) = 'E:\DESTFOLDER'
DECLARE @FilesToCopy TABLE(
id int identity(1,1)
,filetocopy nvarchar(256)
)
INSERT INTO @FilesToCopy
SELECT [filename] FROM [staging].[filelist]
DECLARE @i as int = 1
DECLARE @maxi as int = (SELECT MAX(id) FROM @FilesToCopy)
DECLARE @cmd nvarchar(4000)
DECLARE @act_filetocopy varchar(4000)
WHILE @i <= @maxi
BEGIN
SET @act_filetocopy = (SELECT filetocopy FROM @FilesToCopy WHERE id = @i)
SET @cmd = 'copy "' + @SRCFOLDER +'\' +@act_filetocopy + '" "' + @DESTFOLDER +'"'
print @i, @cmd
--EXEC xp_cmdshell @cmd
SET @i = @i+1
END
I solved this by using robocopy.
DECLARE @filename as varchar(256) = 'metadata_1.xml'
DECLARE @Filefolder as varchar(256) = 'E:\src_folder'
DECLARE @DESTFolder as varchar(256) = 'E:\dst_folder'
DECLARE @mt as int = 128 -- max value = 128
SET @mt = @mt-1 --needs to be corrected since computers count from 0
DECLARE @j as int = 1
DECLARE @maxj as int = @j + @mt
DECLARE @cmd varchar(8000)
DECLARE @act_filetocopy varchar(4000)
DECLARE @i as int = 1
DECLARE @maxi as int = @j +@mt
DECLARE @AllFilesToCopy TABLE(id int identity(1,1),filetocopy varchar(256))
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
IF OBJECT_ID('tempdb..#current') IS NOT NULL DROP TABLE #current
INSERT INTO @AllFilesToCopy
SELECT Bestandsnaam FROM [staging].[METADATASHAREPOINT] where InXML = @filename
SET @maxi = (SELECT COUNT(id) FROM @AllFilesToCopy)
--Loop though list of files
WHILE @i <= @maxi
BEGIN
CREATE TABLE #current (id int identity(1,1),filetocopy varchar(256))
INSERT INTO #current
SELECT filetocopy FROM @AllFilesToCopy where id >= @i and id <= @i + @mt
SET @cmd = 'robocopy '+ @Filefolder + ' ' + @DESTFolder + ' '
SET @j=1
SET @maxj = (SELECT COUNT(id) FROM #current)
-- miniloop to collect a number of files to mention in the robocopy command
WHILE @j <= @maxj
BEGIN
SET @act_filetocopy = (SELECT filetocopy FROM #current WHERE id = @j)
SET @cmd = @cmd +@act_filetocopy +' '
SET @j = @j+1
END
--finalize the robocopy command
SET @cmd = @cmd + '" /MT:'+ CAST(@mt+1 as varchar(3))
--PRINT CAST(@cmd AS NTEXT)
--SELECT LEN(@cmd)
EXEC xp_cmdShell @cmd
SET @cmd = ''
DROP TABLE #current
SET @i = @i +@mt
END