none
SSIS Control Flow with FTP Task completes but Remote Server file does not update RRS feed

  • Question

  • SSIS Control Flow with FTP Task completes but Remote Server file does not update<o:p></o:p>

    Hello, This is my first time setting up an FTP Task in an SSIS Control Flow in order to send files to a remote server.  The remote server requires a username and password.  I believe I have that setup correctly.  I start the Debug and the entire Control Flow process completes without error.  I check the remote server but the file has not updated with today's time stamp.  What is preventing my flat file submission from replacing the remote file?  Can you tell me where I might begin to look or troubleshoot with regard to the disconnect?  I checked the Execution Results but that did not tell me much. 


    Friday, November 17, 2017 5:11 PM

All replies

  • So you're trying to replace an existing file in FTP server?

    Since you say that you didn't get any error can you try placing a new file first and see if it succeeds?

    Also try using FTP client like WinSCP/Filezilla to log on to FTP server and copy/replacing a file with your given credentials into the same folder. This is to rule out the possibility of any access related issues.


    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

    Friday, November 17, 2017 5:14 PM
  • Yes, I am trying to replace data in an existing FTP file.  I removed the original file from the FTP remote server, ran the SSIS package, it completed with success and today's submitted flat file now displays in the FTP remote folder location.  This tells me that my SSIS package is working with regard to sending the file to the remote folder.  However...I waited about 5 minutes, executed the SSIS package again to see if today's file would be refreshed.  It did not work. I refreshed all browsers and the time stamps do not indicate a file has been updated with the most recent data.  I'm willing to try more suggestions. 
    • Edited by mcAngie Friday, November 17, 2017 8:21 PM
    Friday, November 17, 2017 6:16 PM
  • Have you set OverwriteFileAtDest property to true in FTP task?


    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

    Friday, November 17, 2017 6:54 PM
  • Yes.  I made sure it was set a few days ago.  I tried to upload my sample image into this thread but this MSDN site won't allow me to add it until my account has been verified. 

    Here is what I see in my FTP Task Editor:

    IsLocalPathVariable: True

    LocalVariable: User::LocalFilePath

    Operation: Send files

    Is TransferAscii:False

    IsRemotePathVaiable: True

    RemoteVariable: User::RemoteFolder

    OverwriteFileAtDest: True

    //

    • Edited by mcAngie Friday, November 17, 2017 9:34 PM
    Friday, November 17, 2017 9:31 PM
  • Did you try using a FTP client program like WinSCP to see if you can modify the file from it using your credentials?

    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

    Saturday, November 18, 2017 10:04 AM
  • I downloaded WinSCP this morning and I was able to modify the remote file using my credentials.  It does look like I do have read/write allowances on that FTP server.  If I change the remote file name by adding "yyyymmddhhmm_mst" to the end of it, return to my SSIS package, execute the package, a new file is added into the remote folder.  This is not ideal since I hope to schedule the package to run on a daily basis. I would prefer to have it overwrite so I don't have to remove unwanted files on that remote server.  I'm still open to other types of troubleshooting ideas. 
    Monday, November 20, 2017 4:55 PM
  • I downloaded WinSCP this morning and I was able to modify the remote file using my credentials.  It does look like I do have read/write allowances on that FTP server.  If I change the remote file name by adding "yyyymmddhhmm_mst" to the end of it, return to my SSIS package, execute the package, a new file is added into the remote folder.  This is not ideal since I hope to schedule the package to run on a daily basis. I would prefer to have it overwrite so I don't have to remove unwanted files on that remote server.  I'm still open to other types of troubleshooting ideas. 

    Ok 
    Now that you say WinSCP works, an alternate method you can try is to call WinSCP from SSIS using Execute Process Task. You just need to generate a dynamic script to upload the file
    the command put can be used for this purpose

    See

    https://winscp.net/eng/docs/script_upload_single_file

    In SSIS you can call WinSCP com component like this

    https://visakhm.blogspot.ae/2012/12/implementing-dynamic-secure-ftp-process.html

    The above article uses sftp protocol but even for normal ftp process steps remain the same only difference would be protocol and port number difference (21 for FTP)

    Also see

    https://winscp.net/eng/docs/howto_ftp_script

    the command will look like this in your case

    open ftp://usename:password@servername:21
    option transfer binary
    put <yourfilename> <folder path>
    exit
    

    All of the parameters can be made dynamic as explained in the article


    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

    Monday, November 20, 2017 5:54 PM
  • I noticed that in the FTP task videos that the demonstrator would edit the data inside the local folder then execute his package. Whenever he made the edits the remote file would update.  I edited my Data Flow Task and removed the header record from my local file (since this would indicate a change to the file data), remapped the columns, executed my package and the FTP Task completed as expected with the remote file receiving a new date-time stamp as well as displaying the new data in the file.  I'm confident that the FTP Task overwrite function is working.  I have since edited my Data Flow Task to return the header file to my local file, re-mapped my columns, executed the package and confirmed new data displays in the remote file.  In summary, unless there is a change to the data, the remote file will not update.  Am I correct to make this presumption?
    • Edited by mcAngie Tuesday, November 21, 2017 4:27 PM
    Tuesday, November 21, 2017 4:24 PM
  • I am still interested in verification of my last inquiry on 11/21.  Although, I believe I was able to confirm my own question today regarding the FTP Task file update logic.  Our business had been closed due to the Thanksgiving Holiday break, I checked the remote server and the file on the FTP server had not been updated since 11/21/2017.  Our clerical staff returned today and I assume there was a change to the data which resulted in the file being updated on the remote server today. The good news is there was no need to add a dynamic process task to our SSIS FTP project after all.  I do want to thank you for your helpfulness and it appears our issue is resolved. Best Regards. 
    Monday, November 27, 2017 11:53 PM
  • Hey McAngie,

    This is way late for confirmation, but here goes. I had the very same issue. I'm glad I came across your post.

    I was testing using the same file each time and noticed (frustratingly) the file not being uploaded to the server. It took me days to find your post. After reading your post I I modified the content of the file and voila! it uploaded.

    Thank you and yes you correct in your observation: unless there is a change to the data the remote file will not update.

    Thursday, October 10, 2019 12:38 PM