Different way to use WinSCP within SSIS using a Script task

Advertisement

pturner
Joined:
Posts:
6

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;
}

Reply with quote

Advertisement

LOHITH NR
Guest

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....";

Reply with quote

Advertisement

You can post new topics in this forum