ftp upload

[system]

I looked around on the forum but not able to exactly execute it for some reason using the custom transfer program.
Requirement
Upload flat file to ftp location at the end of the data extraction from sql database.
Custom batch program to upload ftp file ftp.bat in C:/ drive
@echo off

set USER=%1
set PASSWORD=%2
set LOCAL_DIR=%3
set FILE_NAME=%4
set LITERAL_HOST_NAME=%5
set REMOTE_DIR=%6

echo open %LITERAL_HOST_NAME%>>ftpcommand.txt
echo %USER%>>ftpcommand.txt
echo %PASSWORD%>>ftpcommand.txt
echo lcd %LOCAL_DIR%>>ftpcommand.txt
echo cd %REMOTE_DIR%>>ftpcommand.txt
echo ascii>>ftpcommand.txt
echo get %FILE_NAME%>>ftpcommand.txt
echo disconnect>>ftpcommand.txt
echo bye>>ftpcommand.txt

DI Job design
SC->DF

SC = Set all the ftp parameter as below
$AW_USER =‘userid’;
$AW_PASSWORD =‘password’;
$AW_LOCAL_DIR =‘C:\Documents and Settings\datawarehouse\My Documents'’;
$AW_FILE_NAME=‘test.csv’;
$AW_Host =‘10.10.10.176’;
$AW_Remote_Loc=‘\inbox\volrec'’;

DF = Transfer data from source sql table to flat file

In the flat file I have select yes for custom transfer program and then in
program executable: C:\ftp.bat
Arguments:$AW_USER $AW_PASSWORD $AW_Local_Dir $AW_File_name $AW_HOST $AW_Remote_LOC
I have left User Name and password empty.

The issue is that the global variable value for $AW_User, $AW_Password, etc do not pass and it stays empty. So my ftpcommand.txt file is as below

ftp
$AW_Local_Dir
$AW_File_name
lcd $AW_HOST
cd
ascii
put $AW_Remote_LOC
disconnect
bye

instead of

ftp 10.10.10.176
userid
password
lcd C:\Documents and Settings\datawarehouse\My Documents
test.csv
cd \inbox\volrec
ascii
put test.csv
disconnect
bye

What am I missing here [/b]

---

thakks (BOB member since 2006-10-24)

[system]

Do we have to set this as environment variable as shown in the attached file. I did that as well but still parameter value do not get replaced in the file

---

thakks (BOB member since 2006-10-24)

[system]

Any dummies guide to upload the flat file to ftp server through custom transfer program will be much appreciated. I did set the environment variable using set_env and later check value using get_env function. I can confirm that I can see the correct value within the DI but I am still not sure how the whole thing will work to upload the file to ftp using ftp.bat file listed below
@echo off

set USER=%1
set PASSWORD=%2
set LOCAL_DIR=%3
set FILE_NAME=%4
set LITERAL_HOST_NAME=%5
set REMOTE_DIR=%6

echo FTP %LITERAL_HOST_NAME%>>ftpcommand.txt
echo %USER%>>ftpcommand.txt
echo %PASSWORD%>>ftpcommand.txt
echo lcd %LOCAL_DIR%>>ftpcommand.txt
echo cd %REMOTE_DIR%>>ftpcommand.txt
echo ascii>>ftpcommand.txt
echo put %FILE_NAME%>>ftpcommand.txt
echo disconnect>>ftpcommand.txt
echo bye>>ftpcommand.txt

---

thakks (BOB member since 2006-10-24)

[system]

Hey Hey…I found the solution…It was silly me but to help other colleague like me, here is the dummies guide on how to use the customer transfer program to upload the file to ftp location. I hope the same can be replicate for downloading file from ftp location.

Step 1
Create Environment Variable (EV) $AW_FileName, $AW_HOST, $AW_LOCAL_DIR, $AW_PASSWORD, $AW_USER.
To create EV, right click on My Computer, then properties, Advanced and at the bottom you will have option for EV.
Click on New under System Variable and not User Variable.
You can leave the value empty as we can set this up in DI

Step 2
Create a batch file by copy paste the script below in to your notepad and save it as ftp.bat under C:\ drive

@echo off

set USER=%$AW_USER%
set PASSWORD=%$AW_PASSWORD%
set LOCAL_DIR=%$AW_LOCAL_DIR%
set FILE_NAME=%$AW_FILE_NAME%
set LITERAL_HOST_NAME=%$AW_Host%
set REMOTE_DIR=%$AW_Remote_Loc%

echo OPEN %LITERAL_HOST_NAME%>>ftpcommand.txt
echo %USER%>>ftpcommand.txt
echo %PASSWORD%>>ftpcommand.txt
echo lcd %LOCAL_DIR%>>ftpcommand.txt
echo cd %REMOTE_DIR%>>ftpcommand.txt
echo ascii>>ftpcommand.txt
echo put %FILE_NAME%>>ftpcommand.txt
echo disconnect>>ftpcommand.txt
echo bye>>ftpcommand.txt

ftp -s:ftpcommand.txt >>ftp.out

Step 3
Now create New Flat File Format called FTPFile under Data Integrator (Data Services).
Select Local as Location or as per your setup
Root directory as C:
File Name as test.csv
Select the format based on your end data requirement.
Over here under Customer transfer program option, select Yes
Now options for Custom Transfer will be enable and enter c:\ftp.bat for Program executable or wherever you saved the file in Step 2.
For Argument, enter values as below. It doesnt need to be in any specific order. But make sure all the variable listed below are entered.
$AW_USER $AW_PASSWORD $AW_LOCAL_DIR $AW_FILE_NAME $AW_HOST $AW_REMOTE_LOC

Step 4
Create a Data Flow job, with data source as your preferred database and then destination as flat file format that you created in Step 3.

Step 5
Enter Script at the beginning of the data flow to set the value for these environment variable as below and to check the correct value is setup. Please replace value as per your ftp setup

set_env(‘$AW_USER’,‘YourFTPUserName’);
set_env(‘$AW_PASSWORD’, ‘YourFTPPassword’);
set_env(‘$AW_LOCAL_DIR’,‘C:\’);
set_env(‘$AW_FILE_NAME’,‘Test.csv’); #Replace Test.Csv to whatever name you gave in Step 3
set_env(‘$AW_HOST’,‘YourFTPServer’);
set_env(‘$AW_REMOTE_LOC’,‘FTPServerLocationIfNotDefault’);

Print(get_env ((‘$AW_USER’)));
Print(get_env ((‘$AW_PASSWORD’)));
Print(get_env ((‘$AW_LOCAL_DIR’)));
Print(get_env ((‘$AW_FILE_NAME’)));
Print(get_env ((‘$AW_Host’)));
Print(get_env ((‘$AW_Remote_Loc’)));

Now save the job and run and if everything set up as per above it should work.

---

thakks (BOB member since 2006-10-24)