Using SQL’s BCP and CMDShell to Encrypt and FTP

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);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s