none
A package run couple months suddenly failed

    Dotaz

  • I have a package upload files to sftp server. The package calls 38 subpackages. The package was scheduled running in sql server, worked fine for couple months but failed for last two days. I try the following:

    1.  I login to the server, open the package ran it and found out which subpackage failed.

    2.  Open  this first failed subpackage, check all the coding, setting is fine, ran it and failed. Then I close the Visual studio, ran it and failed. Then I restart my computer, it works fine.

    3.  I went back to run the package again, after it completed 18 subpackages it failed again.

    4.  Open  the this second failed subpackage, check all the coding, setting is fine, ran it and failed. Then I restart my computer, it still doesn’t work.

    All the failed error message in step 2 and 4 are:  "WinSCP.SessionRemoteException: Copying fles to remote side failed. Error code 4". But I don't think this message has anything to do with fail. I may be wrong.

    I don’t know how to fix it or where to check. any suggestion is appreciated.

    Thank you.


    čtvrtek 12. července 2018 23:19

Všechny reakce

  • Hi MDXLaotu,

    Sounds like errors on WinSCP side, you can have a try following ways:

    1.Renaming a file to a name of already existing file.
    2.Creating a directory that already exists.
    3.Moving a remote file to a different filesystem (HDD).
    4.Uploading a file to a full filesystem (HDD).
    5.Exceeding a user disk quota.

    SFTP Status/Error Codes

    Also please make sure you have proper permission to access the FTP Sever, generally using the proxy account to schedule the job. Remember to update the password if your password expired.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    pátek 13. července 2018 2:59
  • Thank you Pirlo!

    I separate two session instead of one session in my code.  It works but I still don't know why when I used the same code (put all together instead separate WinSCP with others) some subpackages work and some packages don't work. Even the same subpackage it worked for couple months then stop working.

    pátek 13. července 2018 23:28
  • What was the WinSCP script you used? 

    Looks like WinSCP scrip issue to me

    Are you generating it dynamically?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    sobota 14. července 2018 6:06
  • The original code:

     try
                    {
                        using (Session session = new Session())
                        {
                            session.ExecutablePath = sExecPath; // @"D:\SSISProjects\winscpAssembly\winscp.exe";


                            session.Open(sessionOptions);


                            // Upload files
                            TransferOptions transferOptions = new TransferOptions();
                            transferOptions.TransferMode = TransferMode.Binary;
                            transferOptions.PreserveTimestamp = false;


                            TransferOperationResult transferResult;
                            transferResult = session.PutFiles(Targetpath, sSourceDirectory, false, transferOptions);

    string command = string.Format("sudo mv {0} {1}", sSourceDirectory + fileName, sDestDirectory + fileName);
    var startInfo = new ProcessStartInfo();
    startInfo.FileName = sPLink; 
    startInfo.Arguments = string.Format("-ssh {0}@{1}:22 -pw {2} -P 22 {3}",
    sUser, sHost, sPassword, command);


    var process = new Process { StartInfo = startInfo };
    process.Start();
    process.WaitForExit();




                            // Throw on any error
                            transferResult.Check();


                              //close connection
                            session.Close(); 
                         }

                        Dts.TaskResult = (int)ScriptResults.Success;
                    }
                    catch (Exception e)
                    {
                        //MessageBox.Show(e.ToString());
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                }

    Change to:

     try
                {
                    // Setup session options
                    SessionOptions sessionOptions = new SessionOptions
                    {
                        //Set the properties like username & password
                        Protocol = Protocol.Sftp,
                        PortNumber = 22,
                        HostName = sHost,
                        UserName = sUser,
                        Password = sPassword,
                        //SshPrivateKeyPath = sPrivateKeyPath,
                        SshHostKeyFingerprint = sHostKey
                    };


                    try
                    {
                        using (Session session = new Session())
                        {
                            session.ExecutablePath = sExecPath; // @"D:\SSISProjects\winscpAssembly\winscp.exe";


                            session.Open(sessionOptions);


                            // Upload files
                            TransferOptions transferOptions = new TransferOptions();
                            transferOptions.TransferMode = TransferMode.Binary;
                            transferOptions.PreserveTimestamp = false;


                            TransferOperationResult transferResult;
                            transferResult = session.PutFiles(Targetpath, sSourceDirectory, false, transferOptions);

                            // Throw on any error
                            transferResult.Check();


                              //close connection
                            session.Close(); 
                         }


                        string command = string.Format("sudo mv {0} {1}", sSourceDirectory + fileName, sDestDirectory + fileName);
                        var startInfo = new ProcessStartInfo();
                        startInfo.FileName = sPLink; 
                        startInfo.Arguments = string.Format("-ssh {0}@{1}:22 -pw {2} -P 22 {3}",
                                                             sUser, sHost, sPassword, command);


                        var process = new Process { StartInfo = startInfo };
                        process.Start();
                        process.WaitForExit();


                        Dts.TaskResult = (int)ScriptResults.Success;
                    }
                    catch (Exception e)
                    {
                        //MessageBox.Show(e.ToString());
                        Dts.TaskResult = (int)ScriptResults.Failure;
                    }
                }

    úterý 17. července 2018 14:19