Unpredictable text file corruption during transfer?

Advertisement

Guest

Unpredictable text file corruption during transfer?

I am transferring CSV files from a cloud server to my local server for import to SQL via SFTP. I am frequently getting corruption -- records cut off, records cut off with subsequent records appended to them, etc. I can look at these files on the server, prior to transfer, and they are correct -- but changed when I get them after WinSCP transfer.

Example of 2 records after transfer - note that the first record has a full date as the last "field," but the 2nd record's last field is missing the 0 on 3000 (but NOT the ending quotation!)
"9934237","Active","09/05/2015","Conversion","Cert FT","Y","02/20/2013","01/01/1900","07/01/2015","01/01/3000"
"9934238","Terminated","09/05/2015","T-Assignment Ended","Sub","","02/13/2013","11/30/2013","11/30/2013","01/01/300"

CRLF = end of line character.

WinSCP version 5.9 build 6786
Windows Server 2012 R2
SFTP protocol

I have a script, but am not sure if it's right:
@echo off
"\\appdev2012\c$\Program Files (x86)\WinSCP\WinSCP.com" ^
/refresh ^
/log="\\appdev2012\c$\users\sisdbuser\desktop\!Y!M!D_!T_GetFiles.log" /ini=nul ^
/command ^
"open sftp://my.name%%40mydomain:Kronos%%40%%21%%40%%23@xfer.remotesite.net/ -hostkey=""ssh-rsa 2048 keyhere"" -rawsettings Compression=1 CacheDirectories=0 CacheDirectoryChanges=0 PreserveDirectoryChanges=0 EOLType=1 SFTPMaxVersion=3" ^
"cd /Prod/WIM_OUT/Tech" ^
"lcd \\Appdev2012\c$\Users\sisdbuser\Downloads" ^
"get -transfer=ascii -neweronly CCSD_*.csv" ^
"exit"

set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
echo Success
) else (
echo Error
)

exit /b %WINSCP_RESULT%

Log file (modified for security, I hope) is attached. I don't see "errors" in it, but maybe I missed them.

I am (usually) executing my script file from a SQL server query window, using EXEC master..xp_CMDShell "UNC path to script name"

Please point me in the right direction.
  • 20160902_131311_GetFiles.log (23.22 KB, Private file)

Reply with quote

Advertisement

cara
Joined:
Posts:
8

I thought I was logged in when I submitted this -- it belongs to me.

An additional note: I have tried automatic, binary and ascii transfers.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
41,517
Location:
Prague, Czechia

Re: Unpredictable text file corruption during transfer?

Thanks for your report.

I can look at these files on the server, prior to transfer
How exactly are you doing that?

If you re-download the same file again immediately, do you get the same corruption? Or is the problem temporary?

Are you sure you can reproduce the problem with a binary mode? Can you post a log file for that?

Reply with quote

cara
Joined:
Posts:
8

martin wrote:

How exactly are you doing that?
I have a Citrix connection to the vendor's cloud server. When I log into that, I can run Visual Studio over their citrix connection. From within visual, I can File > Open and point to the CSV file that resides on that server, and open it with Notepad.

martin wrote:

If you re-download the same file again immediately, do you get the same corruption? Or is the problem temporary?
The problem SEEMS to change each time the files are downloaded. Sometimes, no corruption. Sometimes, even if run quickly after another download, I'll get DIFFERENT corruption. This has not been well-tested, however.

martin wrote:

Are you sure you can reproduce the problem with a binary mode? Can you post a log file for that?
I have a log file for a previous run using the binary option. It takes some effort to "cleanse" those files of identifying info, so I'll try to work on that. I see no errors in it, however.

I've had to move to another FTP software, because I cannot spend any more time on bad transfers -- but I like WinSCP better, so I would still like to resolve this, if possible. So far, I'm not having the problem using the other software.

P.S. Don't know why my quote tags aren't working!

Reply with quote

Advertisement

Guest

Unpredictable text file corruption during transfer?

I am back, after having temporarily given up on WinSCP because the data was corrupting on transfer. How do I contact you to fix my forum login? The system says that someone is using my user name and my email address -- but not together.

WinSCP has the features I need, but continues to corrupt data being transferred. I'm now handling my downloaded data one record at a time, into my database, so it's no longer failing completely -- but I am missing people, as a result in my target database, and that will be a reason I have to switch away from WinSCP again, if I can't resolve it.

Here's an example of a file (redacted) that gets transferred/downloaded -- this is data up to and through the first GOOD record (the first partial field is preceded by a long series of null characters):
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                *-****","NuXXnt","PaXXXcia","E","","Ms","","12/31/1946","Female","09/05/2015","07/30/2007","01/01/3000"
"9910115","***-**-****","BrXXn","CXXXl","ElXXXtt","","Ms","","09/27/1946","Female","09/05/2015","08/28/1978","01/01/3000"

Here's the powershell script that is doing this sporadically. I am NOT a trained programmer, I piece together what I need from snippets stolen from across the web, so I'm sure it's not perfect:


cls
$UserName = "myemail@address.com"
$Password = Get-Content 'C:\users\sisdbuser\documents\CloudSFTPSecurePass.txt' | ConvertTo-SecureString
$cred = New-Object Management.Automation.PSCredential $UserName, $Password

$localDir = "C:\Users\sisdbuser\Documents\EmployeeData\"
$logfileDir = "C:\users\sisdbuser\documents\logs\"
$logfileName = "$(get-date -f yyyyMMdd_HHmm)_FROMCloud_StaffData.log"

$remoteDir = "/Prod/WIM_OUT/Tech/"
$FileFilter = "CCSD*.CSV"

$emailBody = "see \\AppDev2012\c$\users\sisdbuser\Documents\EmployeeData\`r`n"
$msg = new-object Net.Mail.MailMessage
$msg.From = 'NoReply@address.com' 
$msg.To.Add("myemail@address.com")
$SMTPServer='webmail.@address.com'; #'192.106.2.53'; 
$SMTPClient=New-Object Net.Mail.SmtpClient($SmtpServer,25); 

Function LogWrite
{
   Param ([string]$logstring)

   Add-content $logfileDir$logfileName -value $logstring
}

add-content $logfileDir$logfileName -value "started $(get-date)" -force


 Add-Type -Path (Join-Path "C:\Program Files (x86)\winscp" "WinSCPnet.dll")

try
{
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = "xfer.Cloud.net"
        UserName = $cred.UserName
        Password = $cred.GetNetworkCredential().Password    
        SshHostKeyFingerprint = "ssh-rsa 2048 fingerprint:here"
    }
    
    
    $session = New-Object WinSCP.Session

    $session.Open($sessionOptions)
     
     
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::ASCII

        $transferResult = $session.synchronizeDirectories([WinSCP.SynchronizationMode]::Local,$LocalDir,$remoteDir,$False,$False)

        $transferResult.Check()
        
        if ($TransferResult.Downloads.count -ge 1)
        {
        foreach ($transfer in $TransferResult.downloads) # $getfiles)
        {
            
            $TransferredFilename = $transfer.FileName.replace($remotedir,'').padright(40,' ') 
            
            $FileStamp = $session.getfileinfo($transfer.filename).lastwritetime #$transfer.lastwritetime
            
            LogWrite                  "Download of $TransferredFilename dated $FileStamp succeeded" #`r`n"  #| out-file -append $logfileDir$logfileName
            $emailBody = $emailBody + "{0,-40} {1,-25}" -f "Download of $TransferredFilename","dated $FileStamp succeeded`r`n"
            
            $msg.Subject="Staff Data file(s) found for downloading from Cloud [see body of message for details]"
            $msg.IsBodyHtml = $False
            $msg.Body = $emailBody 
            }
            }
        
     else 
     {
     LogWrite    "No  changed Staff Data files found in on Cloud Cloud to download [End of message]"
     $msg.Subject="No changed Staff Data files found in on Cloud Cloud to download [End of message]"
     
     exit 0}
    
    }

finally
{
    if ($session.length -ge 0)
    {
   
        $session.dispose()
        if ($msg.Subject -gt "")
        {
            $SMTPClient.Send($msg);
        }
    }
    
    # clear session variables 
    $sessionoptions=""
    $session=""
    $transferOptions=""
    $localDir=""
    $FileFilter=""
    $File=""
    $transferResult=""
    $logfile=""
    $Transfer=""
    $TransferredFilename=""
    $msg=""
    $att=""
    $FileStamp=""
    $remoteDir=""
    $logfileDir=""
    $logfileName=""
    $localcopyDir=""
    $emailBody=""
    exit 0
}

catch [Exception]
{
    Write-Host ("Error: {0}" -f $_.Exception.Message)
    # clear session variables 
    $sessionoptions=""
    $session=""
    $transferOptions=""
    $localDir=""
    $FileFilter=""
    $File=""
    $transferResult=""
    $logfile=""
    $Transfer=""
    $TransferredFilename=""
    $msg=""
    $att=""
    $FileStamp=""
    $remoteDir=""
    $logfileDir=""
    $logfileName=""
    $localcopyDir=""
    $emailBody=""
    exit 1
}

Reply with quote

Guest

Re: Unpredictable text file corruption during transfer?

Anonymous wrote:

I am back, after having temporarily given up on WinSCP because the data was corrupting on transfer.
Yesterday's transfer had corruption, and today's seems to have worked flawlessly. It's the unpredictability that's frustrating! If you see anything wrong in my script, please let me know. Otherwise, I guess I'll just re-report if it re-occurs.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
41,517
Location:
Prague, Czechia

Re: Unpredictable text file corruption during transfer?

Anonymous wrote:

I am back, after having temporarily given up on WinSCP because the data was corrupting on transfer. How do I contact you to fix my forum login? The system says that someone is using my user name and my email address -- but not together.
You had typo in your email address. I have corrected it. Try to reset your password now.

To debug the problem, enable Debug-2-level logging. Use the Session.DebugLogLevel = 2. Once you reproduce the problem, please attach a log file showing upload of the corrupted file. And provide an original of the corrupted file as well as the corrupted file.

Reply with quote

cara
Joined:
Posts:
8

DebugLogLevel=2 not working?

Referring to my previously-posted Powershell script, I tried adding this right after "$session.Open($sessionOptions):"
$session.DebugLogLevel =2
and I get error
Exception setting "DebugLogLevel": "Specified argument was out of the range of valid values.
Parameter name: Logging level has to be in range 0-1"
At line:1 char:1
+ $session.DebugLogLevel =2
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], SetValueInvocationException
+ FullyQualifiedErrorId : ExceptionWhenSetting

Please advise.

Thanks

Reply with quote

Advertisement

cara
Joined:
Posts:
8

Sorry -- still don't seem to be able to change level to 2

Thanks, Martin, I got your emails and have downloaded & extracted the Dev version of Winscp.

All I did was change the
Add-Type -Path (Join-Path "C:\Program Files (x86)\winscp" "WinSCPnet.dll")
command in my previously-posted Powershell script to reflect the folder where the dev version lives.

I then added in "$session.DebugLogLevel =2" after the $session.open command. I put a breakpoint on the $session.open, and it seems to open. I step into the debugloglevel command, and the script seems to fail.

I shifted my Catch statement above my Finally statement (where it should've been all along), and I now get an error out, when this happens -- and it's still that "Logging level has to be in range 0-1."

Where would I find the Winscp debug logs, for the session?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
41,517
Location:
Prague, Czechia

Re: Sorry -- still don't seem to be able to change level to 2

Please double-check that you are loading the assembly, I've sent you.
There's no "Logging level has to be in range 0-1." message in this version. You must be loading the old one.

You will find the log, where you point the Session.SessionLogPath to.

Reply with quote

cara
Joined:
Posts:
8

Added SessionLogPath

Thanks, Martin - not sure what was happening - without changing the assembly load, it's now working to set debugloglevel to 2.

I've set the SessionLogPath and am now waiting for the transfer to corrupt -- which, because of all your work, it will probably NOT do, of course. I'll update this thread if/when I get another error, and send the log.

Thanks

Reply with quote

Advertisement

cara
Joined:
Posts:
8

Corruption occurred - what to do with debug log file?

martin wrote:


Can you post a log file for that?

Okay - I had some corruption during download, last night. I confirmed that the original file, on the server, is NOT corrupted, but the file is once downloaded.

I have a debug log file, but it contains a LOT of info (I transfer about 15 files at a time) and some identifying info I don't want to post - and cleanup would be laborious.

How much of the log file do you need to take a look, for me-- just the info related to the specific corrupted file?

thanks

Reply with quote

martin
Site Admin
martin avatar

Re: Corruption occurred - what to do with debug log file?

cara wrote:

How much of the log file do you need to take a look, for me-- just the info related to the specific corrupted file?
That and the log header. Thanks.

Reply with quote

Advertisement

You can post new topics in this forum