Pull file from SFTP site using Biztalk, but with same filename every day

Posted on 2011-10-21
Last Modified: 2012-05-12
I need to pull a file from an SFTP/SSH server using Biztalk 2010, but the new file has the same name every day and only the timestamp on the file changes. I'm currently trying to use the bLogical adapter to accomplish this, but though it pulls the file without issue, I don't want to delete it when I'm done, and I only want to pull and process it again if the timestamp has changed (ie the file on the remote server has been updated).

Is there an obvious way to do this, or am I out of luck with the bLogical adapter, and have to look at something like nSoftware SFTP?
Question by:Ryan McCauley
    LVL 16

    Expert Comment

    I have an outside-the-box idea that isn't eactly what you are looking for but I'll post it anyway since this question is currently marked "neglected" ...

    What if you used a Robo-FTP service to do the logic of transferring the file if it is newer and then the Biztalk logic can just watch a local folder for the new file to appear.

    Basically, my idea is that you have 2 new folders... one is the Robo-FTP scratch folder and the other is the folder that Biztalk is watching for a file.  The scratch folder will always have a copy of the last file downloaded from the server but the watched folder will only have a file if a new file appeared on the remote SFTP server.

    I made an example script to express my idea.  Every day at 6am, the script will "wake up" and delete yesterday's old file out of the folder watched by Biztalk process.  The scratch folder still has a copy of yesterday's file.  The script connects to the SFTP site and downloads the file only if it is newer.  Robo-FTP's download command has an /ifnewer option that will make the download fail (cause error) if the remote file is not newer than the local file with the same name.  So, if there is an error, the script waits 15 minutes and then tries again. This would apply to other types of errors also so if the remote file is just plain missing or if the internet connection is down it will keep trying every 15 minutes until it gets a newer file.  Once we have a newer file, we copy it into the watched folder and then go to sleep until 6am the new morning.

    With this in place your Biztalk logic becomes much easier.  Since the first thing we do is delete yesterday's file that means if there is a file in the watched folder after 6am then it must necessarily be the new file.

    ;; remove existing file from folder watched by biztalk
    DELETE "c:\biztalk\watched\folder\myData.xml"
    ;; download only if remote file is newer than copy of file in scratch folder
    WORKINGDIR "c:\scratch\folder"
    FTPLOGON "" /user="UserID" /pw="Secret" /servertype=SFTP /trust=all
    RCVFILE "myData.xml" /ifnewer 
    IFERROR GOTO failure
    ;; copy file to biztalk watched folder then sleep until 6am tomorrow
    COPY "myData.xml" "c:\biztalk\watched\folder\"
    PAUSE /until=06:00
    GOTO top
    ;; on failure, wait 15 minutes and try again
    CRON "@every15min"
    GOTO top

    Open in new window

    You could use the Robo-FTP Service Installer set this thing to run as a Windows Service and then the OS will make sure it is automagically running all the time, even if the computer reboots.  

    Hopefully this script will give you some ideas even if this solution is out of bounds.
    LVL 28

    Accepted Solution

    Thanks for the suggestion - though I'd prefer a solution native to Biztalk, I was losing hope as SFTP doesn't seem to be something they want to natively support and my other options are limited. I'll try this solution Monday or Tuesday and I expect it will perform as expected - I'll accept your answer if it does. Thanks!
    LVL 28

    Author Closing Comment

    by:Ryan McCauley
    This solution, though not ideal, did solve my problem since the SFTP adapter in Biztalk lacked native support for only fetching a new copy of the file. Thanks for your help!

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you are trying to access the server, have you ever encountered "The terminal server has exceeded the maximum number of allowed connection" error?  or "The user is attempting to log on to a Terminal Server in Remote Administration mode, but the …
    This is a fairly complicated script that will install the required prerequisites to install SCCM 2012 R2 on a server.  It was designed under the functional model in order to compartmentalize each step required, reducing the overall complexity.  The …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now