Post a reply

Options
Add an Attachment

If you do not want to add an Attachment to your Post, please leave the Fields blank.

(maximum 10 MB; please compress large files; only common media, archive, text and programming file formats are allowed)

Options

Topic review

LOHITH NR

Please help me how to script task for loading file to winScp

Details
string WinSCPExePath = @"C:\Users\lohith.rajachar\AppData\Local\Programs\WinSCP";
string SFTPUserName = "abc";
string SFTPPassword = "abc";
string SFTPHostname = "111111";
string fileToUpload = @"\Allergan_DataBase_Dump";
string SFTPTargetPath = "Flat File Connection Manager";
string SFTPsshKey = "ssh-rsa 2047....";
martin

Re: Different way to use WinSCP within SSIS using a Script task

Thanks for sharing this.
Based on this example, I have created article on using the WinSCP .NET assembly from SSIS:
https://winscp.net/eng/docs/library_ssis
pturner

Different way to use WinSCP within SSIS using a Script task

Must have WinSCP installed

  • Run gacutil -i "C:\Program Files\WinSCP\WinSCP.dll" within the command prompt, as an admin
  • Within your SSIS Script task, add a reference to the dll file using the browse task
  • add using WinSCP;
  • your Main() method can now look similar like the following:

public void Main()
{
    string strFTPPath = Dts.Variables["FTPFolderPath"].Value.ToString();
    string strDestination = Dts.Variables["DestinationFolder"].Value.ToString();
    bool fireAgain = false;
 
    strFTPPath = strFTPPath + @"/";
 
    Dts.Events.FireInformation(0, "FTP Path Used:", strFTPPath, "", 0, ref fireAgain);
    Dts.Events.FireInformation(0, "Destination Path Used:", strDestination, "", 0, ref fireAgain);
 
    try
    {
        // Setup session options
        SessionOptions sessionOptions = new SessionOptions
        {
            Protocol = Protocol.Sftp,
            HostName = "FTP server",
            UserName = "User",
            Password = "Password",
            SshHostKey = "ssh-rsa xxxx xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
        };
 
        using (Session session = new Session())
        {
            // Connect
 
            session.ExecutablePath = @"C:\Program Files\WinSCP\WinSCP.exe";
            session.Open(sessionOptions);
 
            // Upload files
            TransferOptions transferOptions = new TransferOptions();
            transferOptions.TransferMode = TransferMode.Binary;
 
            TransferOperationResult transferResult;
            //transferResult = session.PutFiles(@"d:\toupload\*", "/home/user/", false, transferOptions);
            transferResult = session.GetFiles(strFTPPath, strDestination, false, null);
 
            // Throw on any error
            transferResult.Check();
 
            // Print results to output box
            foreach (TransferEventArgs transfer in transferResult.Transfers)
            {
                Dts.Events.FireInformation(0, "Files Uploaded:", "", "", 0, ref fireAgain);
            }
            session.Dispose();
        }
 
        //return 0;
    }
    catch (Exception e)
    {
        Dts.Events.FireInformation(0, "Error: ", e.Message.ToString(), "", 0, ref fireAgain);
        Dts.TaskResult = (int)ScriptResults.Failure;
        //return 1;
    }
 
    Dts.TaskResult = (int)ScriptResults.Success;
}