Yes, It is possible to do by using SSIS.
But, we do not have any built in feature in SSIS, we can use custom code for the same.
To achieve this, you need to use WinSCP a third party component. Download latest version of winscp. Install the exe file and place WinSCPnet.dll in C:\windows\assembly\ folder
Take a script task and add reference to the WinSCPnet.dll and place this below code snippet in code area of script task (change the code accordingly as per your requirement):
public void Main()
{
string hostName = (string) Dts.Variables["User::HostName"].Value;
string userName = (string) Dts.Variables["User::UserName"].Value;
string password = (string) Dts.Variables["User::Password"].Value;
string sshHostKeyFingerprint = (string) Dts.Variables["User::SshHostKeyFingerprint"].Value;
string localoutpath = "C:\\Inbound\\"; /* path where you want to place the downloading the file */
string xmlfilename = "myfile.xml"; /* name of the file to be downloaded e.g. */
string ftpRemoteDirectory = "//Clients/xyz//"; /* remote/dropbox path from where file need to be downloaded e.g. */
string ExecutablePath = "C:\\Program Files (x86)\\WinSCP\\WinSCP.exe"; /* path where you have installed winscp.exe executable e.g.*/
SessionOptions sessionOptions = new SessionOptions
{
Protocol = Protocol.Sftp,
HostName = hostName,
UserName = userName,
Password = password,
SshHostKeyFingerprint = sshHostKeyFingerprint
};
try
{
using (Session session = new Session())
{
session.ExecutablePath = ExecutablePath;
// Connect
session.Open(sessionOptions);
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
TransferOperationResult transferResult;
transferResult = session.GetFiles(ftpRemoteDirectory + xmlfilename, @"" + localOutPath + @"\", 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("Download of {0} succeeded", transfer.FileName),
null, 0, ref fireAgain);
}
}
Dts.TaskResult = (int)DTSExecResult.Success;
}
catch (Exception e)
{
Dts.Events.FireError(0, null,
string.Format("Error when using WinSCP to download files: {0}", e),
null, 0);
Dts.TaskResult = (int)DTSExecResult.Failure;
}
}
Let me know, if will not solve your issue or any clarification here.
Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com
Upload files to Box.com
