ETL and Data Integration tools - Modern ETL software for everyone

Hi..
I’m continuing in the review of the Advanced ETL Processor and I understand that the Processor can download files from the FTP location.
I’ve already set up an FTP connection.
Now I’m trying to go out to the FTP location, look for a particular file mask, decode the file, download it to a directory (so that the raw data file can be saved) and then load it to a table.
Under the Packages, I’ve set up an FTP Package and from here I was putting in the actions.
When I put in a Mask, all of the files instead of just the one I want to be downloaded are downloaded. I’ve tried putting in the full name of the file and that doesn’t same to work.
Is the purpose of the Mask field to tell the action what file to look for or is it for something else? Also, on a daily basis, the file mask would pretty much be the same with the exception of the date being the current date. For example, I want to pull in the MBSAll.rep.YYYYMMDD for a given day and we would pull this file in daily M-F, how would I need to set this up in the actions so the process would know to look for today’s file?

Gladys

Gladys

Documentation page 213
23. File names functions


This chapter provides list of functions which can be used in file names or directories

Examples:

C:\Data{GetSystemDate('YYYYMMDD')}.vis
<LogDirectory>{GetSystemDate('YYYYMMDD')}.vis

Basically all you need to do is to put
MBSAll.rep.{GetSystemDate('YYYYMMDD')}

Into mask box

Mike

Hi Mike..
do I have to set up a step to get the system date first before I try to download the file from the ftp site? When I put ‘MBSAll.rep.{GetSystemDate('YYYYMMDD')}’ in the Mask field and try to execute, I get the message below:

Right clickon FTP object and select set as starting point

Also watchour online ETL tutorials
www.etl-tools.com/etl-tools/advanced-etl...online-tutorial.html

Mike

Hi Mike..

I’m still having problems with just downloading the one file from the ftp site. It’s still downloading all of the files that are on the ftp site. All I want to pull in is the MBSAllrep. file for the most current day, but everything gets pulled in. I’ve looked at the tutorial but that doesn’t seem to help.

Gladys

Created two files on our ftp site
MBSAllrep.20100825 and
MBSAllrep.20100824

Using mask MBSAllrep.{GetSystemDate('YYYYMMDD')}

Package log

Information 25/08/2010 21:42:06 Preparing to execute: FTP
Information 25/08/2010 21:42:06 Package Started
Information 25/08/2010 21:42:06 Executing Ftp (Download): DB Softlab Download
Information 25/08/2010 21:42:06 Connecting to: ftp.dbsoftlab.com
Information 25/08/2010 21:42:07 Connected!
Information 25/08/2010 21:42:07 Changing Remote directory to: /www/www
Information 25/08/2010 21:42:07 Done!
Information 25/08/2010 21:42:07 Getting List of files
Information 25/08/2010 21:42:07 Mask: MBSAllrep.20100825
Information 25/08/2010 21:42:08 Done!
Information 25/08/2010 21:42:08 Found: 1 File(s)
Information 25/08/2010 21:42:08 Files : MBSAllrep.20100825

Information 25/08/2010 21:42:08 Downloading MBSAllrep.20100825
Information 25/08/2010 21:42:09 Successfully downloaded MBSAllrep.20100825
Information 25/08/2010 21:42:09 Finished Execution Ftp: DB Softlab Download
Information 25/08/2010 21:42:09 Package Finished

FTP log

Information 25/08/2010 21:42:06 Stat Connected.
Information 25/08/2010 21:42:06 Recv : 220 64.14.68.81 FTP server ready
Information 25/08/2010 21:42:06 Sent : HOST ftp.dbsoftlab.com
Information 25/08/2010 21:42:06 Recv : 500 HOST not understood
Information 25/08/2010 21:42:06 Sent : SYST
Information 25/08/2010 21:42:07 Recv : 215 UNIX Type: L8
Information 25/08/2010 21:42:07 Sent : FEAT
Information 25/08/2010 21:42:07 Recv : 211-Features:
LANG en
MDTM
UTF8
AUTH TLS
PBSZ
PROT
REST STREAM
SIZE
Information 25/08/2010 21:42:07 Recv : 211 End
Information 25/08/2010 21:42:07 Sent : OPTS UTF8 ON
Information 25/08/2010 21:42:07 Recv : 200 UTF8 set to on
Information 25/08/2010 21:42:07 Sent : USER tools
Information 25/08/2010 21:42:07 Recv : 331 Password required for tools
Information 25/08/2010 21:42:07 Sent : PASS xxxxxxxx
Information 25/08/2010 21:42:07 Recv : 230 User tools logged in
Information 25/08/2010 21:42:07 Sent : FEAT
Information 25/08/2010 21:42:07 Recv : 211-Features:
LANG en
MDTM
UTF8
AUTH TLS
PBSZ
PROT
REST STREAM
SIZE
Information 25/08/2010 21:42:07 Recv : 211 End
Information 25/08/2010 21:42:07 Sent : OPTS UTF8 ON
Information 25/08/2010 21:42:07 Recv : 200 UTF8 set to on
Information 25/08/2010 21:42:07 Sent : TYPE I
Information 25/08/2010 21:42:07 Recv : 200 Type set to I
Information 25/08/2010 21:42:07 Sent : CWD /www/www
Information 25/08/2010 21:42:07 Recv : 250 CWD command successful
Information 25/08/2010 21:42:07 Sent : TYPE A
Information 25/08/2010 21:42:08 Recv : 200 Type set to A
Information 25/08/2010 21:42:08 Sent : PORT 192,168,0,2,205,3
Information 25/08/2010 21:42:08 Recv : 200 PORT command successful
Information 25/08/2010 21:42:08 Sent : LIST MBSAllrep.20100825
Information 25/08/2010 21:42:08 Recv : 150 Opening ASCII mode data connection for file list
Information 25/08/2010 21:42:08 Recv : 226 Transfer complete
Information 25/08/2010 21:42:08 Sent : TYPE I
Information 25/08/2010 21:42:08 Recv : 200 Type set to I
Information 25/08/2010 21:42:08 Sent : PORT 192,168,0,2,205,4
Information 25/08/2010 21:42:08 Recv : 200 PORT command successful
Information 25/08/2010 21:42:08 Sent : RETR MBSAllrep.20100825
Information 25/08/2010 21:42:08 Recv : 150 Opening BINARY mode data connection for MBSAllrep.20100825 (13030 bytes)
Information 25/08/2010 21:42:09 Recv : 226 Transfer complete
Information 25/08/2010 21:42:09 Sent : QUIT
Information 25/08/2010 21:42:09 Stat Disconnected.