The following article is a walkthrough of the CodePlex open source SSIS extensions for SFTP, PGP encryption and Zip archiving.

Resources

Download the appropriate version of the SSIS extensions, and the SFTP mini-server..

1. SSIS Extensions - SFTP Task, PGP Task, Zip Task

•      Web Site: http://ssisextensions.codeplex.com/releases/view/101949
•      2008 R2 Download Link: http://ssisextensions.codeplex.com/downloads/get/623584
•      2012 Download Link: http://ssisextensions.codeplex.com/downloads/get/616818

2. Core FTP Mini SFTP Server

•      Web Site: http://www.coreftp.com/server/
•      Download Link: http://www.coreftp.com/server/download/msftpsrvr.exe

Install and configure the SSIS extensions

Run the "Installer SSIS Extensions SQL 2008 R2.msi" or "Installer SSIS Extensions SQL 2012.msi file" downloaded above.

After the extensions have been installed start "Microsoft Visual Studio" and create a new SSIS package, then right-click on the "Toolbox" panel and select "Choose Items" from the popup menu.

Select the "SSIS Control Flow Items" tab from the "Choose Toolbox Items" panel, add checkmarks to the "PGP Task," "SFTP Task" and "Zip Task" checkboxes, then click the "OK" button.

The PGP, SFTP and Zip tasks have been added to the toolbox and are ready for use in SSIS packages.

Create SQL Server demo data

Open SQL Server Managment Studio and create a database named TestDB. Download the attached "sp500hst.zip" resource file and extract the "sp500hst.txt" file it contains to a folder on your computer. Run the following SQL script to create and populate a table named "TestDB.dbo.sp500hst."

Note: Be sure you change the code to match your source folder.

/*
     Change 'C:\source_directory\' to the folder containing the 'sp500hst.txt'
     demo file.
     If you get a 'file not found' error, it probably means your are referencing
     a database on a remote server. To run the script you need to copy the file
     to a network folder and reference it with the fully-qualified UNC path, ie. 
     '\\ComputerName\SharedFolder\sp500hst.txt.'
*/USE TestDB
BEGIN TRY
    DROP TABLE [dbo].[sp500hst]
END TRY
BEGIN CATCH
END CATCH
GO
CREATE TABLE [dbo].[sp500hst] (
    [Date] [VARCHAR](10) NULL,
    [Ticker] [VARCHAR](10) NULL,
    [Open] [VARCHAR](10) NULL,
    [High] [VARCHAR](10) NULL,
    [Low] [VARCHAR](10) NULL,
    [Close] [VARCHAR](10) NULL,
    [Volume] [VARCHAR](10) NULL
) ON [PRIMARY]
GO
BULK INSERT [dbo].[sp500hst] FROM 'C:\source_directory\sp500hst.txt' WITH (FIELDTERMINATOR = ',')
GO
SELECT * FROM [dbo].[sp500hst]

Select from the TestDB.dbo.sp500hst table to confirm that it contains the test data.

Configure demo directories

Create file folders named "c:\testfiles\" and "c:\sftp\."

Go to http://ianpurton.com/online-pgp/ to generate a PGP public key.

Paste the public key into a text file named "pgp_public_key.txt" and save it to the "c:\testfiles\" folder.

Alternatively, you can download the attached "pgp_public_key.txt" resource file and save it to the "c:\testfiles\" folder.

At this point the "c:\testfiles\" directory looks like this...

...and the "c:\sftp\" directory looks like this.

Configure and start the mini SFTP server

Double-click the "msftpsrvr.exe" file downloaded above, configure the server with user "username," password "password," port "22" and root path "c:\sftp\," then click the "Start" button.

Configure the SSIS package

Download the attached "SFTP_PGP_ZIP.dtsx" resource file and open it in "Microsoft Visual Studio" to display the task components and the configuration variables passed from the "Create data file" script task to the SFTP, PGP and Zip task components.

Double-click on the "TestDB" connection manager and configure it to point to the location of the test data created above.

Execute the SSIS package

Click on the "Start Debugging" button to execute the SSIS package.

The SFTP mini server displays log messages when the transfers occur.

After package execution is complete...

...the "c:\testfiles\" directory looks like this...

...and the "c:\sftp\" directory looks like this.

This is what the "c:\testfiles\market.csv" file looks like.

Script task code

The script task queries the sp500hst table and writes to a file named "market.csv."

Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim file_name As String = Dts.Variables("file_name").Value.ToString
        Dim file_directory As String = Dts.Variables("file_directory").Value.ToString
        Dim file_path As String = file_directory & file_name
        Dim zip_target_root As String = file_name & ".zip"
        Dim zip_target As String = file_directory & zip_target_root
        Dim pgp_target_root As String = file_name & ".pgp"
        Dim pgp_target As String = file_path & ".pgp"
        Dim pgp_public_key As String = file_directory & "pgp_public_key.txt"
        Dts.Variables("file_path").Value = file_path
        Dts.Variables("zip_target_root").Value = zip_target_root
        Dts.Variables("zip_target").Value = zip_target
        Dts.Variables("pgp_target_root").Value = pgp_target_root
        Dts.Variables("pgp_target").Value = pgp_target
        Dts.Variables("pgp_public_key").Value = pgp_public_key
        Dim FileToDelete As String = file_path
        If System.IO.File.Exists(FileToDelete) = True Then
            System.IO.File.Delete(FileToDelete)
        End If
        Dim mySqlStatement As String = "SELECT [Date],[Ticker],[Open],[High],[Low],[Close],[Volume] FROM [dbo].[sp500hst]"
        Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("TestDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
        Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
        Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Dim file As System.IO.StreamWriter
        file = My.Computer.FileSystem.OpenTextFileWriter(file_path, True)
        Dim sLine As String
        Do While reader.Read()
            sLine = reader("Date").ToString
            sLine += "," & reader("Ticker").ToString
            sLine += "," & reader("Open").ToString
            sLine += "," & reader("High").ToString
            sLine += "," & reader("Low").ToString
            sLine += "," & reader("Close").ToString
            sLine += "," & reader("Volume").ToString
            file.WriteLine(sLine)
        Loop
        file.Close()
        reader.Close()
        myADONETConnection.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

It also sets the values of the parameters used by the PGP, Zip and SFTP custom task components.

Suppress spurious warnings

When the "DelayValidation" property one of the custom task components is set to "False," the task component will display a warning icon.

Set the "DelayValidation" property to "True" for all the custom task components to override spurious warnings.

Configuration of SFTP, PGP and Zip task components

Double-click each task component to bring up its task editor. The configuration parameters for each component are selected from drop-down lists in its task editor panel. This is how the PGP task component is configured.

This is how the Zip task component is configured.

This is how the SFTP task component that transfers the encrypted file is configured.

This is how the SFTP task component that transfers the zipped is configured.