FTP Input & Output

OL Connect Professional Workflow
1

Hi Guys,

I’ve got quite an easy task but I’m not 100% sure where to start exactly.

I have file in a FTP Input folder. Steps I would like to take is the following:

  1. Connect to FTP drive

  2. Get the actual filename of .OMA file.

  3. Save it to a variable.

  4. Connect to a SQL DB and run a query with that file name variable.

  5. Save the result to a variable.

  6. Rename the file using the variable.

  7. Repeat this for all files in the drive with .OMA file extensions.

I would really appreciate any help I can get with this.

2

  1. An FTP input task will handle this. You’ll use a mask to only pick up the files you want. In this case *.OMA

  2. The original filename is stored in the system variable %o so you can use that to access the orignal name after it’s been downloaded by the FTP input task.

  3. It’s already in a variable, but for safekeeping, you can use Set Job Infos and Variables to store it in a local variable.

  4. Use the PlanetPress Database plugin to query your SQL Database. Since you’re using variable data as part of the query, you’ll also have to use a dynamic SQL query (second tab of the plugin).

  5. The results of your query are returned in a new data file, allowing you to read a value out from it. Note, at this point you will have lost your original data file (overwritten by the query). You can avoid this by putting the PPress Database plugin in a branch. In that branch you’ll do your query and get your variable, but when you leave the branch your original data file (input from FTP) will be restored.

  6. You’ll have to upload the file back to FTP. The FTP Output plugin is what you’ll use and it has an option to rename the file as it outputs. So you can use your variable here.

  7. Input tasks repeat automatically until there are no files left to capture that meet capture criteria. So this process will already loop.

There is one pretty significant downside to all of this, that’s the necessity to download the files for processing and then re-uploading them. If you’re talking about huge files, this is not going to be very efficient. If you’re looking to rename them in place without downloading/uploading, you’re going to need to write some sort of script that connects to the FTP, gets the name, interacts with the SQL database, and renames the files one by one. You might be able to accomplish this in VBScript or through a winscp script as detailed here: Advanced files rename on SFTP/FTP server :: WinSCP