I have used Renci SSH for doing this exact thing for a while. It's a free library you can use with SSIS. Works great.
Just drop a script task on the control flow where you want to SFTP the file. I use variables for setting things like login/password, paths, file names, etc.
Get the library from codeplex.
Add the DLL to your SSIS package reference.
Add the USING to your SSIS code in the script.
Example below is how to SFTP that in the SSIS package. I use it to generate an XML file to send to an SFTP site.
using System;
using System.IO;
using Renci.SshNet;
namespace ABC123.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
string XMLString = "";
string CurrentProcessGroup = "";
string XMLFileName = "";
string ProcessFolder = "";
string FTPServer = "";
string FTPUser = "";
string FTPPassword = "";
string FTPFolder = "";
XMLString = Dts.Variables["XMLOutput"].Value.ToString().Replace("<ROOT><ROOT>", "<ROOT>").Replace("</ROOT></ROOT>", "</ROOT>");
CurrentProcessGroup = Dts.Variables["CurrentProcessGroup"].Value.ToString();
ProcessFolder = Dts.Variables["ProcessFolder"].Value.ToString();
XMLFileName = ProcessFolder + CurrentProcessGroup.Replace("'", "").Replace(" ", "") + ".xml";
bool KeepXMLFiles = System.Convert.ToBoolean(Dts.Variables["KeepXMLFiles"].Value);
bool FTPFiles = System.Convert.ToBoolean(Dts.Variables["FTPFiles"].Value);
if (FTPFiles)
{
if (!System.IO.Directory.Exists(ProcessFolder))
{
System.IO.Directory.CreateDirectory(ProcessFolder);
}
if (System.IO.File.Exists(XMLFileName))
{
System.IO.File.Delete(XMLFileName);
}
GenerateXmlFile(XMLFileName, XMLString);
FTPServer = Dts.Variables["FTPServer"].Value.ToString();
FTPUser = Dts.Variables["FTPUser"].Value.ToString();
FTPPassword = Dts.Variables["FTPPassword"].Value.ToString();
FTPFolder = Dts.Variables["FTPFolder"].Value.ToString();
string folderName = FTPFolder;
string fileName = XMLFileName;
string absoluteFileName = Path.GetFileName(XMLFileName);
string FullName = folderName + "SubFolderName." + absoluteFileName;
try
{
using (var client = new SftpClient(FTPServer, 22, FTPUser, FTPPassword))
{
client.Connect();
if (!client.IsConnected)
{
throw new Exception("Not Connected");
}
using (FileStream fs = File.OpenRead(fileName))
{
client.UploadFile(fs, FullName, true, null);
}
client.Disconnect();
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
throw;
}
if (System.IO.File.Exists(XMLFileName))
{
if (!KeepXMLFiles)
{
System.IO.File.Delete(XMLFileName);
}
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public void GenerateXmlFile(string filePath, string fileContents)
{
StreamWriter objStreamWriter;
objStreamWriter = new StreamWriter(filePath);
objStreamWriter.Write(fileContents);
objStreamWriter.Close();
}
}
}