Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SFTP, encrypt or compress data files in SSIS using custom components

By Stan Kulp,

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

2. Core FTP Mini SFTP Server

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.

Resources:

sp500hst.zip | pgp_public_key.txt | SFTP_PGP_ZIP.dtsx
Total article views: 5533 | Views in the last 30 days: 48
 
Related Articles
BLOG

Configure Windows Server 8 ISCSI Target

In part 4 will configure Windows Server 8 ISCSI Target to complete SQL Server 2012 Next Gen HADR lab...

FORUM
BLOG

SSIS: Script Component Asynchronous Transformation

The SSIS script component can be configured to use synchronous or asynchronous outputs.  If the scri...

ARTICLE

Dynamically Download FTP Files Using SSDT

Dynamically initialise FTP connection using expressions and retrieve files stored in multiple FTP di...

BLOG

Powershell default start directory

This post discusses some methods in configuring the Powershell  default start directory  By default...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones