custom transfer function in flat file by using FTP

[Former Member]

Hi,

     i want to use custom transfer function in flat file,i have seen .dat file in tec manual regarding custom transfer function as below

echo off

set USER=%1

set PASSWORD=%2

set LOCAL_DIR=%3

set FILE_NAME=%4

set LITERAL_HOST_NAME=%5

set INP_FILE=ftp.inp

echo %USER%>%INP_FILE%

echo %PASSWORD%>>%INP_FILE%

echo lcd %LOCAL_DIR%>>%INP_FILE%

echo get %FILE_NAME%>>%INP_FILE%

echo bye>>%INP_FILE%

ftp -s%INPT_FILE% %LITERAL_HOST_NAME%>ftp.out

please explain the above code and what changes i have to made to this code if i use FTP as custom transfer function

[Former Member · Accepted solution]

Hi Michael,

To use Custom transfer ftp, first you need to have the create  .bat file in the job server.

1) Copy the below given commands into a text file and save it as <anyname>.bat
    echo off

    set USER=%1
    set PASSWORD=%2
    set LOCAL_DIR=%3
    set REM_DIR=%4
    set FILE_NAME=%5
    set HOST_NAME=%6
    set TYPE=%7

    set INPUT_FILE=%FILE_NAME%.ftp

    echo %USER%>%INPUT_FILE%
    echo %PASSWORD%>>%INPUT_FILE%
    echo lcd %LOCAL_DIR%>>%INPUT_FILE%
    echo cd %REM_DIR%>>%INPUT_FILE%
    echo %TYPE% %FILE_NAME%>>%INPUT_FILE%
    echo bye>>%INPUT_FILE%

    ftp.exe -s:%INPUT_FILE% %HOST_NAME% >D: .log
    del %INPUT_FILE%

2) Note : Above .bat is  for getting as well as putting the file.

3) Once the bat is created and copied to job server you can call the .bat from your custom transfer.

4) Enable the Custom transfer in file format.

5) If we enable the custom transfer options are enabled.
Enter the executable and argument in file editor as below

Executable : <complete path>\<anyname>.bat
Username : Username for the FTP server
Password : Password for the FTP user
Arguments: $AW_USER $AW_PASSWORD [$$Local_File_Path] [$$Remote_File_Path] $AW_FILENAME [$$HostName] get
note : give space between each argument

      : To put file give last argument as put instead of get

Screenshot attached

Thanks,
Ancy

[Former Member · Accepted solution]

Hi Ancy,

           thanks for the help,i have some doubts

             1.what file path and file name i have to give in file format window

             2.could you please explain use of arguments like

             $AW_USER $AW_PASSWORD [$$Local_File_Path] [$$Remote_File_Path] $AW_FILENAME                                [$$HostName] get

[Former Member · Accepted solution]

Hi Michael,

Details  below :

In Data file options in file editor:

File Path : The local file path accessible for job server where the file to be placed.

File Name : name of the file you need to get/put.

In Custom Transfer options in file editor:

$AW_USER : Is a system variable.It will automatically picks up the value given for user name in custom transfer option

$AW_PASSWORD : Is a system variable.It will automatically picks up the value given for password in custom transfer option

[$$Local_File_Path] : Local file path accessible for job server where the file to be placed.(should be same as the path given above in file editor)

[$$Remote_File_Path] : path in FTP server where the file is residing/where file to be moved

$AW_FILENAME : Is a system variable.It will automatically picks up the value given for file name in Data file option in file editor

[$$HostName] : The server to which FTP connection to be made.

get/put : pass get if to get the file from FTP server  & put if the file is moved to FTP srever

Note : I have given substitution parameters for Local file path,Remote File path,HostName.You can directly also give the path here.

Thanks,

Ancy

[Former Member · Accepted solution]

Hi Ancy,

i have created a file format using custom transfer , as you said.

but it is showing warnings while executing the job as below

finally job ends with an error "can not open file" as below

please help me...

[Former Member · Accepted solution]

Hi Michael,

For confirmation:

1) Do User1 has access to localhost?.

2) Also for remote path I could see only \ is given.Please provide entire path.

3) Also check if the ftp script will work in your system using cmd(command prompt) .

4)Execute the script step by step  by passing the correct values.

If it is working in cmd it should work in Custom transfer.

I doubt if same system can be used as source/ftp server.

Please check and let me know for any queries.

Thanks,
Ancy

[Former Member · Accepted solution]

Hi ancy,

i am using filezilla server and client in my system,user "user1"  have access to the ftp server default path "/" .

ftp working fine for Excel  workbooks in my system.

how can i execute the code one by one in cmd. please help me

[Former Member · Accepted solution]

Hi Michael,

To execute the script step by step:

type each command as below:

    ftp <ftp system name>

    enter <username>

    enter <password>

    lcd <LOCAL_DIR>( the local directory that has file)

    cd <REM_DIR>(the directory in remote server to which file is to be moved)

    get <filename>

    bye

Thanks,
Ancy

[Former Member · Accepted solution]

Hi ancy,

            i have already tried it ,it is working fine

and ftp server log is as below

when i execute the job with file as source having code(myftp.bat) in custom transfer,i did not find any log as above

i think code which is mentioned in tech manual is not hitting the FTP server

what changes i have to made to that code

[Former Member · Accepted solution]

Hi Michael,

Did you try invoking the myftp.bat in cmd?

Try invoking the myftp.bat and pass parameters specified commanline.

If that is also working fine...Then we need to check if any settings in BODS.

For me it is working  and also in log nothing will be displayed other than invoking command of custom transfer.

If file is not moved,it will be available in trace log.

Thanks,
Ancy