Zip the PDF Files and Encrypt it through Command shell

Create Procedure [dbo].[File_Transfer]

(

@SourceDirectory varchar(max)

)

As begin

--select top(1) @SourceDirectory=Sourcelocation from test

---Declare @SourceDirectory varchar(2000)

Declare @E_command varchar(2000)

DECLARE @ls_cmd VARCHAR(2000) -- must not be NVARCHAR(MAX) or VARCHAR(MAX) to work with xp_cmdshell

Declare @archivecode varchar(10)

Declare @password varchar(20)

Declare @DestPreDirectory varchar(2000)

Declare @Filename varchar(50)

Declare @DestinationFullDirectory varchar(1000)

set @filename=SUBSTRING(@SourceDirectory,(select charindex ('.',@SourceDirectory)-5),5)

--SET @ls_cmd = '""E:\Program Files\7-Zip\7z.exe"  a  "o:\backup\Destination\test1.zip" -ppassw0rd123 "o:\backup\Source\test1.txt""'

SET @ls_cmd = 'E:\Program Files\7-Zip\7z.exe'

set @archivecode='a'

set @password='-ppassw0rd123'

set @DestPreDirectory='\\destination\file\' --- Destination Directory for the file needs to be zipped with password protected.

--set @Filename='test1' --- this files name needs to be retrived from the Source Directory PDF filename

set @DestinationFullDirectory=@DestPreDirectory+@Filename+'.zip'

--set @E_command= '"'+'"'+@ls_cmd+'"'+' '+@archivecode+' '+'"'+@DestinationFullDirectory+'"'++' '+@password+' '+'"'+@SourceDirectory+'"'+'"'

set @E_command= '"'+'"'+@ls_cmd+'"'+' '+@archivecode+' '+'"'+@DestinationFullDirectory+'"'++' '+@password+' '+'"'+@SourceDirectory+'"'+'"'

PRINT @E_command

PRINT ''

--use the windows user who has access to network share. 

EXECUTE AS login = 'domain\username'

EXEC xp_cmdshell @E_command

End

GO