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\."
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.
