• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 798
  • Last Modified:

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

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?
0
Ryan McCauley
Asked:
Ryan McCauley
  • 2
1 Solution
 
AlexPaceCommented:
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.

:top
;; 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 "sftp.mydomain.com" /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
:success
FTPLOGOFF
COPY "myData.xml" "c:\biztalk\watched\folder\"
PAUSE /until=06:00
GOTO top

;; on failure, wait 15 minutes and try again
:failure
FTPLOGOFF
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.
0
 
Ryan McCauleyAuthor Commented:
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!
0
 
Ryan McCauleyAuthor Commented:
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!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now