I inherited a project where I needed to create a physical file of data, encrypt it, and FTP it daily. First, I created my script, and when it was completed, added it to the SQL Agent jobs. Code snippets follow:
BCP to Export Data
DECLARE @sql varchar(8000); SET @returnCode = 0; DECLARE @returnCode int; SELECT @sql = 'bcp "select * from ##MyExportTable; " queryout "myfile.csv" -T -c -t"," -CRAW' EXEC @returnCode = master..xp_cmdshell @sql
CMD Shell to Encrypt
Window’s shell doesn’t have any native encryption, I’ve found a third party tool with command-line extensions to use and call it.
SELECT @sql = 'C:\..encryptionprogram.exe -switches MyKeyName myfile.csv 2> encryptionerrors.txt' exec @returnCode = xp_cmdshell @sql ; if ( @returnCode <> 0 ) RAISERROR( N'Encryption failed', 10, 1);
CMD Shell to FTP
SELECT @sql = 'C:\...ftpprogram.com -switches MyDestination myfile.csv 2> ftperrrors.txt' exec @returnCode = xp_cmdshell @sql ; if ( @returnCode <> 0 ) RAISERROR( N'FTP failed', 10, 1);