Menu Request Demo

Transmitting Export Files to Customer's FTP Site Using Host Script

Date:
15 January 2013
Author:
Raphael Klebanov

WhereScape RED has an easy way of creating Export objects that are used to produce ASCII files from a single db table or view for a downstream feed. The solution below represents a very typical post-Export task: secure delivery of generated files to a customer’s FTP site with use of a RED Host script.

RED Host scripts are maintained within the WhereScape RED environment and can be scheduled to run in their host environments (either WINDOWS or UNIX). The sample below contains examples for several common practices such as FTP load using secure credentials, use of RED parameters, wildcard file load, and archiving.

You will need to add five RED parameters in order to execute the Host script as illustrated below:

OUTFilePath               = Directory where outbound files are created by the WhereScape RED extract;
ArchiveFilePath          = Directory where processed files are archived;
FileSpec                      = File spec of the files to FTP and archive;
FtpUserName             = FTP username;
FTPPwd                       = FTP password.
FTPPwd                       = FTP password.

The result will be something like this. Also ensure that the path parameters have an ending backslash.

image 1 table

The following environment variables will be set by the WhereScape RED Host script

WSL_SEQUENCE    = A unique sequence number for the scheduler;
WSL_WORKDIR       = The work directory defined in the connection;
WSL_SERVER          = The server defined in the connection;
WSL_DATABASE     = The database defined in the connection;
WSL_USER               = The DSS user defined in the connection;
WSL_PWD                 = The DSS password defined in the connection.

The sample connection might be as follows:

image 2 table

Below is a sample (working) script that can be copied and used by WhereScape customers.

REM **********************************************************
REM Script Name : ftprap
REM Description : Transmit export files to customer’s FTP site
REM Generated by : WhereScape RED, manually
REM Generated for: WhereScape Customer
REM Author : Raphael Klebanov
REM **********************************************************
@echo off
setlocal enabledelayedexpansion
setlocal enableextensions

::Setthe directory where multiple outgoing files are created by RED extract
SET SEND_FILE_PATH=$POUTFilePath$
::Set the file specification of the files to FTP and Archive
SET SEND_FILE_NAME=$PFileSpec$
::Set the path of where the files will be archived after being successfully ftp'd
SET ARCHIVE_PATH=$PArchiveFilePath$
::Set the FTP UserName
SET FTPUser=$PFtpUserName$
::Set The FTP Password
SET FTPPwd=$PFtpPwd$
::Set file count variable which tracks how many files we have processed
SET /A FileCount=0
::Set variables of date parts (yy, mm, dd, dow) to be used in the renaming of the file
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (
set dow=%%a
set mm=%%b
set dd=%%c
set yy=%%d
)
::Remaining variables declarations
SET SEND_TABLE=%send_file_path%%send_file_name%
SET FILECTL=%WSL_WORKDIR%\wsl%WSL_SEQUENCE%.ctl
SET FILELOG=%WSL_WORKDIR%\wsl%WSL_SEQUENCE%.log
SET FILEAUD=%WSL_WORKDIR%\wsl%WSL_SEQUENCE%.aud

REM **********************************************************
REM ************* C H E C K F O R F I L E S **************
REM **********************************************************
Dir %SEND_TABLE% /b | %windir%\system32\find /v "RandomString64" >nul && (goto FILES_FOUND) || (set _empty=Empty)
echo -1
echo No Files Found For Upload In Location: %SEND_FILE_PATH%%send_file_name%
exit

:FILES_FOUND
REM **********************************************************
REM ************* F T P T H E D A T A ********************
REM **********************************************************
echo load of %SEND_TABLE% > %FILEAUD%
SET /A RESULT_CODE=1
SET RESULT_MSG=Send Completed

:LABEL_LOAD
SET FILE_NAME=NO_MORE_FILES
for %%A in (%SEND_TABLE%) do if "!FILE_NAME!"=="NO_MORE_FILES" SET FILE_NAME=%%A && SET FILENAMEONLY=%%~nA
if %FILE_NAME%==NO_MORE_FILES goto LABEL_EXIT

::rename file before FTP
SET NewFileName=%FILENAMEONLY%_%yy%%mm%%dd%.CSV
REN %FILE_NAME% %NewFileName% >>%FILEAUD% 2>&1

::Create FTP Script File ftpcmd.scr
echo Open ftp.WhereScape.com > c:\ftpcmd.scr
echo %FTPUser%>> c:\ftpcmd.scr
echo %FTPPwd%>> c:\ftpcmd.scr
echo cd import>> c:\ftpcmd.scr
echo cd TabDelimitedCSVFiles >>c:\ftpcmd.scr
echo mput %SEND_FILE_PATH%%NewFileName%>>c:\ftpcmd.scr
echo bye>>c:\ftpcmd.scr

::Execute the FTP script
%windir%\system32\ftp.exe -i -v -s:c:\ftpcmd.scr >> %FILEAUD% 2>&1

IF %errorlevel% EQU 0 GOTO LABEL_OKAY
:LABEL_FAIL
echo -2
echo Send failed with return status %ERRORLEVEL% See error log for details
rem type %FILEAUD% >&2
rem type %FILELOG% >&2

exit

:LABEL_OKAY
echo FTP Successful For File %FILE_NAME% >> %FILEAUD%

REM **********************************************************
REM ************* A R C H I V E ******************************
REM **********************************************************
::Archive and rename the file

:LABEL_RENAME
MOVE %SEND_FILE_PATH%%NewFileName% %Archive_Path%%NewFileName% >>%FILEAUD% 2>&1

IF %errorlevel% EQU 0 GOTO MOVE_OKAY
:MOVE_FAIL
echo -2
echo %FILE_NAME% Was Unable To Be Moved To %Archive_Path%%FILENAMEONLY%_%yy%%mm%%dd%.CSV >>%FILEAUD%
exit

:MOVE_OKAY
echo %FILE_NAME% Successfully Moved To %Archive_Path%%FILENAMEONLY%_%yy%%mm%%dd%.CSV >>%FILEAUD%

::Since successful, add 1 to the files processed counter
Set /A FileCount+=1

REM **********************************************************
REM ************* N E X T F I L E **************************
REM **********************************************************
REM WARNING: Do not loop back unless the file name contains a wildcard
REM WARNING: You must rename the file that has been loaded if
REM looping back otherwise the script will loop for
REM ever loading the same file.
:LABEL_NEXTFILE
goto LABEL_LOAD

REM **********************************************************
REM ************* E X I T ************************************
REM **********************************************************
:LABEL_EXIT
echo %Result_Code%
echo Number Of Files Processed: %FileCount% %FilePath%
exit

CATEGORIES:

Comments 0

Leave a Comment

No comments.