I needed SFTP Task, as SSIS has a built in FTP task, but this works only for the FTP protocol. I needed to upload a file to SFTP.
For this purpose worked and found that it can be achieved with the help of some third party tools like Putty and WinSCP. I preferred here WinSCP for no reason.
It requires two things to be done :
1) Download and install WinSCP, you can get from the below link
http://winscp.net/eng/download.php
Generally the file installed resided at C:\Program Files (x86)\WinSCP.
2) Download and install the winSCP .NET assembly/COM library. below is the link from where you can get it :
http://winscp.net/eng/docs/library_install
This download has one one winscpnet.dll and some other files like readme.txt and others, out of which winscpnet.dll is usefull for us. WinSCP .NET assembly should be installed to GAC to be accessible.
-----------------------------------------------------------------------------
Now to setup the SSIS Package, we need a sshKey, and to get this follow the below steps :
1) Run the WinSCP.exe from C:\Program Files (x86)\WinSCP(path may be different in your case).
2) Log in to the SFTP server.
3) Go to the Menu and under "Session" click on "Server and protocol information"
4) In the middle get the text below “server host key fingerprint”
IT will be looking like “ssh-rsa 2048 ff:38:aa:cd:12:22:46:78:ab:cd:df:hk:12:43:34:4a:05”
Keep this with you.
Now Setup the SSIS Package :
1) In the ssis package drag a new "script task".
2) Create some variable so that any modifications can be done easily.
To create the variables, right click on the package pane and click variable.
Below variables can be used :
winSCPPath = "C:\Program Files (x86)\WinSCP\winscp.exe"
SFTPUserName = "{username}"
SFTPPassword = "{password}"
SFTPSite = "{ftp.sitename.com or 188.206.123.675}"
LocalPath = "{Local Path from where File to be uploaded}"
RemoteSFTPDirectory = "/home/folderWhereTheFileTobeUploaded"
sshKey = “ssh-rsa 2048 ff:38:aa:cd:12:22:46:78:ab:cd:df:hk:12:43:34:4a:05”;
winSCPLog = true/false
winSCPLogPath = "{path to local folder to save the log file}"
C# Script :
Now double click the "script task" and open the task
Here against ReadWriteVariable, click on the ellipse and check the user variables you created above.
so that these variables can be used in the script.
Now click on "Edit Script", here you will get a window for C# code.
Put your code here.
Note : If you get any error related to session or SessionOptions, do add WinSCP namespace and also add winscpnet.dll from the add "reference"
below is the C# code :
--------------------------------------------------------------------------------------------------
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.AddIn;
using WinSCP;
namespace ST_58a3c528d50348fdad80a80a904f1e20.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
// TODO: Add your code here
string winSCPPath = Dts.Variables["winSCPPath"].Value.ToString();
string username = Dts.Variables["SFTPUserName"].Value.ToString();
string password = Dts.Variables["SFTPPassword"].Value.ToString();
string SFTPSite = Dts.Variables["SFTPSite"].Value.ToString();
string LocalPath = Dts.Variables["RarFolder"].Value.ToString();
string RemoteSFTPDirectory = Dts.Variables["RemoteSFTPDirectory"].Value.ToString();
string sshKey = Dts.Variables["sshKey"].Value.ToString();
Boolean winSCPLog = (Boolean)Dts.Variables["winSCPLog"].Value;
string winSCPLogPath = Dts.Variables["winSCPLogPath"].Value.ToString();
try
{
// Setup session options
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = SFTPSite,
UserName = username,
Password = password,
SshHostKeyFingerprint = sshKey
};
using (Session session = new Session())
{
// If you find that the assembly does not have the WinSCP.exe then you can assign here the path of exe.
session.ExecutablePath = @"C:\Program Files (x86)\WinSCP\WinSCP.exe";
// In case If the version in Assembly and that of the exe path given above mismatches then use disable version check.
session.DisableVersionCheck = true;
// Connect
session.Open(sessionOptions);
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
TransferOperationResult transferResult;
transferResult = session.PutFiles(LocalPath, RemoteSFTPDirectory, false, transferOptions);
// Throw on any error
transferResult.Check();
// Print results
bool fireAgain = false;
foreach (TransferEventArgs transfer in transferResult.Transfers)
{
Dts.Events.FireInformation(0, null,
string.Format("Upload of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
Dts.TaskResult = (int)DTSExecResult.Success;
// return 0;
}
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to upload files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
}
}
}
----------------------------------------------------------------------------------------------------------
Reference: Govind Badkur(
http://sqlserver20.blogspot.com)
