Post a reply

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)


Topic review


SSIS - WinSCP Upload Failure

I did have a feeling that was the case as it is fairly large file and the script that runs does take a long time to run.
As this file is generated once per week - it seems like the logical answer but thought I would try out the forum to see if perhaps there was another issue.
I will try to loop as suggested.


Re: SSIS - WinSCP Upload Failure

This does not look like WinSCP problem.
You probably run your upload script too early, and the process that is creating the file has not finished yet.

Another possibility is that the newly created file is being scanned by an antivirus application, which locks the file, so it cannot be accessed by WinSCP, when it is starting the transfer.

As a workaround, you can loop at the beginning of your script to wait until the file can be opened.

SSIS - WinSCP Upload Failure

I have an SSIS Package that create three separate files and uploads them to a SFTP Server
The problem that keeps occurring is one of the files is 1000 KB and it keeps saying this file is in use when the file was created successfully by the SSIS Package
Here is the exact error that I am getting:

Description: Error when using WinSCP to upload files: WinSCP.SessionRemoteException: Can't open file '<FileName>'. System Error. Code: 32. The process cannot access the file because it is being used by another process at WinSCP.OperationResultBase.Check() at ST_1cff4fb3fdae4184a3e0145198875bbd.ScriptMain.Main() End Error Error: 2020-03-01 05:01:25.89 Code: 0x00000006 Source: Upload File to GoAnywhere Description: The script returned a failure result. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:00:00 AM Finished: 6:00:07 AM Elapsed: 3607.61 seconds. The package execution failed. The step failed.

Below is my script that is getting that error
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using WinSCP;
namespace ST_1cff4fb3fdae4184a3e0145198875bbd
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
   public partial class ScriptMain : VSTARTScriptObjectModelBase
      /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
      public void Main()
            byte[] emptyBytes = new byte[0];
            string FlatFileName = (string)Dts.Variables["User::FlatFileUploadPath"].Value;
            // GoAnywhere SFTP Configuration
            SessionOptions sessionOptions = new SessionOptions
                Protocol = Protocol.Sftp,
                HostName = (string) Dts.Variables["User::SFTPHostName"].Value,
                PortNumber = (int)Dts.Variables["User::SFTPPort"].Value,
                UserName = (string) Dts.Variables["User::SFTPUserName"].Value,
                SshHostKeyFingerprint = (string) Dts.Variables["User::SFTPHostKeyFingerprint"].Value,
                SshPrivateKeyPath = (string) Dts.Variables["User::SFTPPrivateKeyLocation"].Value,
                PrivateKeyPassphrase = (string) Dts.Variables["User::SFTPPassphrase"].Value
                using (Session session = new Session())
                    // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                    // you need to set path to WinSCP.exe explicitly,
                    // if using non-default location.
                    session.ExecutablePath = @"C:\Program Files (x86)\WinSCP\winscp.exe";
                    // Connect
                    // Upload files
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;
                    /*-- NOTE: Enter Destination FTP Folder as provided by GoAnywhere Team --*/
                    TransferOperationResult transferResult = session.PutFiles(
                        FlatFileName, "/To_Televox/", false, transferOptions);
                    // Throw on any error
                    // Print results
                    bool fireAgain = false;
                    foreach (TransferEventArgs transfer in transferResult.Transfers)
                        Dts.Events.FireInformation(0, null,
                            string.Format("Upload 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 upload files: {0}", e),
                    null, 0);
                Dts.Log(string.Format("Error when using WinSCP to upload files: {0}", e), 1, emptyBytes);
                Dts.TaskResult = (int)DTSExecResult.Failure;
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure