Hi All,
I am working on the project where I want to upload the file into SFTP. I know SSIS has FTP Task. I would like to know How can I upload the file into SFTP through SSIS. I know I download the third party SFTP task but the problem is then I have to download this third party SFTP task in QA and Prod as well otherwise it will give me an error in QA and Prod. Am I right?
Please advise.
Thank You.
It will give you an error in the environment that does not have the downloaded task. If you download it into QA and the process works, you will need to duplicate the same steps in prod to have it work there. I am assuming that QA and prod are separate. One of my clients hosts both environments on the same machine using the same DB structure. When I run an un-optimized query, I slow real users down as well. In that environment, only permissions are different and the downloaded tool might work for both.
Thank You for your reply. That's what I thought.
Do you guide me if you have any .NET OR VB Script to download the file from SFTP ?
In previous jobs, I have used a scripting language call WinBatch to automate it. In my current position, I am using a tool called AutoMate to automate it. Both of these are products you would need to purchase. Some sFTP tools have built-in scripting languages. I would Google something like "sFTP scripting automation" to find what is available and free.
If it is just the downloading you need, there are free FTP clients like FileZilla available, and some purchased products have subsets for free. Another product that I think had a free version to download years ago is WSFTP with the purchased product being WSFTP Pro.
I have not used SSIS myself to be able to tell you what might tie in with SSIS, but again, a careful Google search may find you what you need.
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();
}
}
}i download and upload to SFTP using SSIS all the time. The built-in SSIS functions are basically useless for SFTP, though, but that shouldn't stop you from coding just about anything you want using a Script Task and [vb.net or c#.net, whichever you want - I use VB.NET, it's more universally recognized/used).
Simply download PSFTP.EXE, a universally used command line program that can log in to, put, get, etc., with SFTP ... and use a script task to automate the .bat file and command tile (simple text file creation code), then execute it...etc. etc.
PS - and my recommendation doesn't depend on you having to install any 3rd party SSIS add-ins, which would be a stumbling block for dev's at most companies. (mine included).
If you are looking for SFTP file uploding task through SSIS , You can refer this link http://zappysys.com/products/ssis-powerpack/ssis-sftp-task-ftp-ftps,
Here You will get some more retated and relevant task regarding SSIS SFTP task.
Hope It it will be helpful 