Sharing how i got SFTP Task to work

Jan 12, 2016 at 11:27 PM
I finally was able to get it working on my local machine as well as server. It was a bumpy road, but it really works! Many thanks to authors of the code!

The version I loaded was for SSIS SFTP v2.0.0.30 for SQL Server 2012

1. ON my LOCAL MACHINE

I downloaded project locally on my machine,
Opened project as LOCAL ADMIN in VS 2012
Built solution.

As result of build/install, the .dll file was created under:
 C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Tasks    
 C:\Windows\Microsoft.NET\assembly\GAC_32\SSISSFTPTask110\v4.0_2.0.0.30__f7871de73e053501\
You can achieve the same by downloading .dll and then registering in GAC using gacutil.exe or PowerShell (I used PowerShell for install on the server)

Now I worked with VS 2010 to set up SSIS.
Open VS, on SSIS Toolbox, right click and refresh for SFTP Task to appear.

Drag and drop SFTP task into Control Flow window.
SFTP Server : mySFTPurl.com ( no https: part)
User : userid with permissions to sftp site
Password: password for SFTP
Action: Get file ( for example)
Local Path: //where/i/want/to/save/my/files - is your destination folder
Remote Path: /subfolder/from/mySFTPurl.com/where/files/are/*.csv - source subfolder in SFTP

Saved SSIS.

NOTE : Set ‘Run64BitRuntime = FALSE’ in VS.
Go to Project--> [YourProjectName]-Properties-->in Configuration Properties-->Debugging

Good luck!

2. ON the Windows Server 2012 R2, 64 bit

  1. Drop SSISSFTPTask110.dll under D:\MSSQL_X86\110\DTS\Tasks (you should find where subfolder 110\DTS\Tasks is)
  2. To register in GAC, open PowerShell, run as administrator
Set-location "D:\MSSQL_X86\110\DTS\Tasks"
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("D:\MSSQL_X86\110\DTS\Tasks\SSISSFTPTask110.dll")

As result you should find assembly under:
C:\Windows\Microsoft.NET\assembly\GAC_32\SSISSFTPTask110\v4.0_2.0.0.30__f7871de73e053501

Note, if you need to uninstall you can run command
$publish.GacRemove("D:\MSSQL_X86\110\DTS\Tasks\SSISSFTPTask110.dll")

Now you can deploy package to Integration Services in Management Studio.

Note: User can run SSIS from SQL Management Studio on his local machine only if he has install of .dll on his local machine.
User can run SSIS itself by remote login into server and execute from there.
This is somewhat strange , but that's what we saw.

When you create SQL job to run package, in Execution options check option 'Use 32 bit runtime'
__
Permissions required__
To execute package user (sql agent/proxy account) should have access to:
  1. Read access to the folder with assembly C:\Windows\Microsoft.NET\assembly\GAC_32\SSISSFTPTask110
  2. Destination folder (Local path, specified in package)
  3. You should have account to access SFTP
    To install .dll you should have admin permissions on the server
Good luck!