Topic "Unpredictable text file corruption during transfer?"

Author Message
Guest




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

Description: (none)

Advertisements
cara
[View user's profile]

Joined: 2016-09-02
Posts: 3
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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
Thanks for your report.

Quote:
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?
cara
[View user's profile]

Joined: 2016-09-02
Posts: 3
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!
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
cara wrote:
because I cannot spend any more time on bad transfers

So can you help us debugging this or not?
Guest




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):
Code:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                *-****","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:


Code:

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
}


Guest




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.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 25015
Location: Prague, Czechia
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.
cara
[View user's profile]

Joined: 2016-09-02
Posts: 3
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
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License