The following article is a walkthrough of the CodePlex open source SSIS extensions for SFTP, PGP encryption and Zip archiving.
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.