SSIS - WinSCP Upload Failure

Advertisement

vinodj
Joined:
Posts:
2
Location:
New York

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;
#endregion
 
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>
   [SSISScriptTaskEntryPoint]
   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
            };
 
            try
            {
                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
                    session.Open(sessionOptions);
 
                    // 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
                    transferResult.Check();
 
                    // 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
        };
        #endregion
 
   }
}

Reply with quote E-mail

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
33,710
Location:
Prague, Czechia

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.
See https://stackoverflow.com/q/1406808/850848

Reply with quote

vinodj
Joined:
Posts:
2
Location:
New York

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.

Thanks,
Vinod

Reply with quote E-mail

Advertisement

You can post new topics in this forum