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. <\/p>\n
So we broke this task into lots of smaller tasks to get the job done. Below are the steps we took:
\n1. obtain username and password to login to mainframe
\n2. get permissions to the datasets for our login name
\n3. obtain a task server with FTPS connectivity to the mainframe (IT supported)
\n4. Download\/Install and FTPS.exe from ipswitch (aka move-it-freely) to our ftps task server
\n5. Learn and test manual processes for FTPS command line processing of the files.
\n6. Write scripts and batch files to replace manual command line entry
\n7. add scripts to a task scheduler
\n8. test, test, test
\n9. write SQL process for extracting data downloaded from mainframe to SQL Server
\n10. write log files for tracking of files downloaded and processed so we don’t miss any or process any twice.<\/p>\n
Here is a sample of the scripts: Notes:<\/strong> firstfile.txt<\/strong> Notes:<\/strong> 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 … Continue reading
\nmain.bat<\/strong>
\n:file1
\nftps -e:on -z -t:600 -s:firstfile.txt mainframeIPaddress<\/em> > logfile.txt
\nif %errorlevel% == 0 goto file2
\necho an error occurred %errorlevel%
\n:file2
\nftps -e:on -z -t:600 -s:secondfile.txt mainframeIPaddress<\/em> >> logfile.txt
\nif %errorlevel% == 0 goto done
\necho an error occurred %errorlevel%
\n:done<\/p>\n
\n-e, -z, -t are settings that are used by the mainframe connection. See documentation for FTPS.exe for descriptions
\n-s:filename points to a script that has the commands that one would enter if they were using command line
\n>> logfile.txt writes the connection information to a logfile and displays the errors if any.
\n%errorlevel% is the error codes generated when a script can not run.
\ngoto done – Logic to skip over error messaging<\/p>\n
\n username
\n password<\/em>
\n prompt
\n passive
\n cd directory<\/em>
\n lcd localdirectory<\/em>
\n mget dataset.filename(0)<\/em>
\n quit<\/p>\n
\nThe filename(0) gets the latest iteration of the file. (-1) would be the prior days.
\nUsing MGET returns the full file name to the server vs GET returns filename(0) as the filename.<\/p>\n","protected":false},"excerpt":{"rendered":"