Install it, Configure-it & Run-it…

Do you have a 32 bits Windows environment or 64 bits Windows environment?

For 32 bits environment, you have to copy the dll from the SSISSFTPTask110.zip archive to “C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\

For 64bits environment, you have to copy the dll from the SSISSFTPTask110.zip archive to “C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\

I made for you a little PowerShell script that helps you to add-it to GAC (GACInstall.ps1 in attachment – Download page)

Set-ExecutionPolicy Unrestricted; #Confirm from Popup
[Reflection.Assembly]::LoadWithPartialName("System.EnterpriseServices")
[System.EnterpriseServices.Internal.Publish] $publish = new-object System.EnterpriseServices.Internal.Publish;

#32 bits - comment or uncomment in function of your environment
# $publish.GacInstall("C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks\SSISSFTPTask110.dll");
#64 bits - comment or uncomment in function of your environment
# $publish.GacInstall("C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\SSISSFTPTask110.dll");

You return to your Business Intelligence Development Studio aka Visual Studio 2012, you click-right to SSIS Toolbox and the refresh submenu.

image

 

and voilà the component.

image

 

Right now you have to edit its properties. Double-click the component and the following window appears:

image

(the documentation page is under construction Smile )

 

 

Add some user variables to test the component:

image

Let’s configure it:

Please paste the appropriate correspondent between your variables and Control’s properties:

image

…You can configure the action that will be executed at the run time:

Regarding the “Action” section, you can choose one of the following items:

- Send File
- Get File
- Create Remote Directory
- Create Local Directory
- Remove Remote Directory
- Remove Local Directory
- Delete Remote File
- Delete Local File

For ‘Local path’ and ‘Remote path’, you can use the expression builder to edit dynamically your source and destination path, depending on the action you take.

image

Let’s execute the task:

image

…and the Log is:

image

If you want to upload only the files with ‘CSV’ extension, in your variable @SourcePath you can specify a value like ‘C:\Temp\SFTP\*.csv’  and the task will upload only the csv files to the remote directory. In fact you can use the classic search file mask like ‘M*.csv’, that meaning you will upload all the files that start with letter ‘d’ and have the extension csv.

The same behavior is available when you download multiple files or when you want to delete multiple remote files respecting a given mask.

In this case the variable used is User::LocalPathEx and the result after execution is:

image

… so you have a detailed tracking of what it happens.

                         

               If you want to obtain the list of files from a remote folder, you have to select from the action ComboBox the item with the following text: “Get Files List From the Remote Folder” and a new ComboBox “Returned files list” will be visible in the properties window. You have to select an object type variable and eventually you specify a remote path (if not, the root folder is considered as the default remote folder).

image

As you see in the image, I added a Script Task only for test purposes (but practically maybe you will do the same!)

the Script Task contains this code:

public void Main()
{
           List<string> ar = new List<string>();
           ar = (List<string>)Dts.Variables["OutParam"].Value;
           Dts.TaskResult = (int)ScriptResults.Success;
}

Don’t forget to add your variable in the ReadOnly variables list:

image

and the result after execution is:

image

Maybe you already saw something like:

image

… Sleep on disconnect…, so to avoid overloaded connections, you can give it the time to disconnect completely.

An another chapter of the component is the authentication by a private key (only OpenSSH key are allowed):

image

So, after you choose Key Encryption type option, it lust have to specify the private key file variable or “File connector” with the full path to the key file and optionally the ‘Pass Phrase’.

The password is not mandatory in this case.

 

Finally, the main feature of the 1.5 version is the integration of the classic ADO Recordset (Enumerator) provided for example by a SQL Task. Let’s see how it works.

We will create a connection to a database. We suppose a (local) Server.

We imagine a table like:

USE [SFTPFiles]
GO

CREATE TABLE [dbo].[Files](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [path] [nvarchar](1050) NULL,
    [filename] [nvarchar](1050) NULL
) ON [PRIMARY]

…and we consider following query:

SELECT [path] ,[filename] FROM [SFTPFiles].[dbo].[Files] with the following result:

path // index = 0 *

filename // index = 1*

E:\Temp\SSIS Tests\SFTP\CopyFolder\MPTracking20072011.csv

MPTracking20072011.csv

E:\Temp\SSIS Tests\SFTP\CopyFolder\MPTracking24052011.csv

MPTracking24052011.csv

E:\Temp\SSIS Tests\SFTP\CopyFolder\MPTracking28052011.csv

MPTracking28052011.csv

*Keep in mind the index

We will drag into the designer surface a SQL Task, we specify the correct connection, the correct query, anyway, something like this:

image

So, it must to specify “Full Result Set” for ResultSet Property and on the other side it must to specify, the object type variable that will capture the records from your query.

image

The next step is to configure the component:

image

By checking the checkbox “The source file(s) from GET or OUT or DEL operations…”, it will enable the Resultset behavior. So it must to specify the object type variable as you see in the previous screenshot, and then the index of the targeted column, the column that contains the name or the path of the files in local or on remote server. What that means?

For example if my action is equal to “Send File”, the operation is relative to the local environment, so your files are physically on your computer or network. If your action is “Get File”, then the content of the Resultset will be relative to the SFTP server.

As you see, there are two radio buttons “Only the name of the file” & “The full path”.

1. When you choose “The full path”, the source of files (that is relative to the action you take Send of Get file(s) ) will consider the entire path is provided; for Delete operation the reference will be always the remote path

2. When you choose “Only the name of the file”, the final path will be concatenated with the provided path “Local Path” or “Remote Path”, once again relative to your action you take “Send” or “Get”

In fact “Send” & “Get” are PUT & GET commands!

So here we are:

image

and the Progress Tab:

image

So, you can use this feature to PUT some files, TO GET some files and to DELETE some files (only remote).

Enjoy it!

Last edited Jun 16, 2014 at 11:15 PM by cosminfr, version 16