This months challenge has been to extract daily files from the mainframe down to an ftp server where we can utilize the data in SQL Server. The file names changes daily, incrementing by 1 for each generation. The files are also available at different times of the day.
So we broke this task into lots of smaller tasks to get the job done. Below are the steps we took:
1. obtain username and password to login to mainframe
2. get permissions to the datasets for our login name
3. obtain a task server with FTPS connectivity to the mainframe (IT supported)
4. Download/Install and FTPS.exe from ipswitch (aka move-it-freely) to our ftps task server
5. Learn and test manual processes for FTPS command line processing of the files.
6. Write scripts and batch files to replace manual command line entry
7. add scripts to a task scheduler
8. test, test, test
9. write SQL process for extracting data downloaded from mainframe to SQL Server
10. write log files for tracking of files downloaded and processed so we don’t miss any or process any twice.
Here is a sample of the scripts:
main.bat
:file1
ftps -e:on -z -t:600 -s:firstfile.txt mainframeIPaddress > logfile.txt
if %errorlevel% == 0 goto file2
echo an error occurred %errorlevel%
:file2
ftps -e:on -z -t:600 -s:secondfile.txt mainframeIPaddress >> logfile.txt
if %errorlevel% == 0 goto done
echo an error occurred %errorlevel%
:done
Notes:
-e, -z, -t are settings that are used by the mainframe connection. See documentation for FTPS.exe for descriptions
-s:filename points to a script that has the commands that one would enter if they were using command line
>> logfile.txt writes the connection information to a logfile and displays the errors if any.
%errorlevel% is the error codes generated when a script can not run.
goto done – Logic to skip over error messaging
firstfile.txt
username
password
prompt
passive
cd directory
lcd localdirectory
mget dataset.filename(0)
quit
Notes:
The filename(0) gets the latest iteration of the file. (-1) would be the prior days.
Using MGET returns the full file name to the server vs GET returns filename(0) as the filename.
