Usp_copy_unzip_dirfiles--Copy, unzip, list file names from the FTP folder to work Folder

CREATE PROC Usp_copy_unzip_dirfiles
@FTPPath NVARCHAR (4000) = ",--FTP folder path
@WorkPath NVARCHAR (4000) = ",--Work folder path
@FileName NVARCHAR (4000) = ",--file name
@sqlCmd NVARCHAR (4000) = ",--the cmd command to execute
@RegRule nvarchar (4000) = ' Regex*.zip ',--the rule that defines the file name to be copied
@7zippath NVARCHAR (4000) = ' ipcc$\ ' program Files ' \7-zip\7z.exe ',--the principle is called 7z decompression software decompression, so the IPC corresponds to the corresponding network disk path
@IsZipped BIT = 1--Input parameters, confirm that the copy is not a compressed file, only the compressed file, the following script decompression section to be executed
As
SET @sqlCmd = ' dir/b ' [email protected][email protected]
IF object_id (' tempdb.. #dirlisttab ') is not NULL
DROP TABLE tempdb: #dirlisttab
CREATE TABLE tempdb. #dirlisttab
(
FILENAME Nvarchar (128)
)

INSERT into #dirlisttab
EXEC xp_cmdshell @sqlCmd
--select * from #dirlisttab

DECLARE file_cur CURSOR LOCAL STATIC forward_only for
SELECT [FILENAME] from #dirlisttab
OPEN File_cur
While 1=1
BEGIN
FETCH NEXT from File_cur to @FileName
SET @sqlCmd = ' copy ' [email protected][email protected]+ ' [email protected][email protected]
Print @sqlcmd
EXEC xp_cmdshell @sqlcmd

IF (@IsZipped = 1)
BEGIN
SET @sqlCmd = @7zippath+ ' x ' [email protected][email protected]+ '-y-aos-o ' [email protected]
Print @sqlcmd
EXEC xp_cmdshell @sqlcmd
END

If @ @fetch_status <> 0 break;
END
CLOSE File_cur
Deallocate file_cur
SET @sqlCmd = ' dir/b ' [email protected]+ ' *.txt > ' [email protected]+ ' Filename|del ' [email protected]+ ' *.zip/q '
Print @sqlcmd
EXEC xp_cmdshell @sqlcmd
DROP TABLE tempdb: #dirlisttab
GO

Usp_copy_unzip_dirfiles--Copy, unzip, list file names from the FTP folder to work Folder