Recently I answered a question on how to automate a task that runs a DTS package, encrypts the result with PGP and then transfers the encrypted file to a FTP site. I became interested in this subject since I heard the same request from 2 people in less than a month and realized that this is a standard pattern for a business process. Extract.Encrypt.Transfer.
First person asking me this found the solution in having a .NET application designed to perform these task using existing .NET components.
I think the is a easier solution is a PowerShell script.
All ww have to do is script the following:
#execute DTSPackage
DTSRun /S "(DBServer)" /N "DTSPackage" /A "StringPar":"8"="String Value" /A "IntPar":"3"="1" /W "1" /E
#Encrypt result file
gpg -E Datafile.csv --options
#transfer file over to FTP
function Open-FTPConnection($ftphost, $username, $password) {
[void][Reflection.Assembly]::LoadFrom("C:\IndyNet-Daily-20051007\Indy.Sockets.dll")
$ftp = new-object Indy.Sockets.FTP
$ftp.Disconnect()
$ftp.Host = $ftphost
$ftp.Username = $username
$ftp.Password = $password
$ftp.Connect()
$ftp.Passive=$true;
return $ftp
}
function Close-FTPConnection($ftp) {
$ftp.Disconnect();
}
function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {
"Uploading {0} into {1}.." -f $sourceFileName, $targetDir;
$ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);
"Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir;
}
$f = Open-FTPConnection "localhost" "foo" "bar"
Upload-FTPFile $f "C:[\Path]\Datafile.pgp" (Get-FTPCurrentLocation $f)
"Datafile.pgp"
Close-FTPConnection $f
Source code found here:
http://groups.google.com/group/microsoft.public.windows.powershell/msg/6d9b5a4541160b25
Command line GNUPG can be downloaded from here:
Of course this needs some polishing but … this is can be easily reused