Automate FTP from Mainframe 1

[Valeda]

I am not sure if I am in the right forum, but I need to transfer a text file from the mainframe to the SQL Server 2000 or in a directory daily. How would I do this without manual intervention?

 

[mrdenny]

There is an FTP step that can be added to a DTS package which can handle this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Valeda]

Thanks so much for your help. I will try it.

 

[Valeda]

I need a little help. How do I specify the mainframe file? Would I use the directory option?

 

[AlexCuse]

After you set up your connection in the FTP task, click on the 'Files' tab. You should be able to specify files there.

Hope this helps,

ALex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[Valeda]

In setting up the FTP task, it asks there are two options for source, Internet or Directory.

 

[AlexCuse]

I rarely come within spitting distance of a mainframe, so I am kind of in the dark as far as how they work, but couldn't you just put the UNC path where your files are stored into where it asks you for directory?

If not, you may need to connect via the mainframe's IP address as if it were an internet site.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[Valeda]

OK,
I have the IP address for the mainframe. I understand that I will probably need to use the Dynamic Properties Task to specify the file to be downloaded. I am having trouble with that. What would the path for the file look like. Any examples? Any alternatives?

 

[fredericofonseca]

dataset on the mainframe will need to be defined similar to this one.

'ldbp.tmp.job.extract'

the "'" around the name are required. Speak with your mainframe people to determine what is the dataset name.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

Thanks Frederico,
I am getting an error now 'Could not open file for reading'.

 

[fredericofonseca]

That would be permissions or wrong dataset name.

Speak with your mainframe guys to see which one of them it is.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

NOne of them have ever tried this. I must be missing something. What connections and tasks would I need in my DTS? Have you actually accomplished this? The DBA's and mainframe guys here don't have a clue.

 

[fredericofonseca]

for you to try if the problem is the dataset or the permissions, or both, try the same ftp on a command line from the dos prompt


something like this.

ftp ip_address (or server_name)
(enter user and password when requested0

then

get 'my_data_set_name' my_pc_server_name


If this fails then you will have another idea of what is wrong.


And if your mainframe guys have never done a FTP then you better tell your management to start hiring new ones.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

I did try it from the command prompt. That worked. However, I still get the error message when trying to do it from my code. I tried creating a .bat file and executing it.

Call oScript.Run ("ftp.exe -s:" & Server.MapPath("RUNftp.bat"), 0, False)

That also didn't work.

 

[fredericofonseca]

hum. if the command line worked, and the batch you mention now didnt, then i would say you are trying to ftp a file into a location not available at the server.

This is now requiring a bit more of work. Ill try it monday at work and see what i get. Ill then post here a dts package that will work.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

Thanks,
I wish this was a challenge to our mainframe guys. They just raise their hands. Thanks so much for your help and efforts.

 

[fredericofonseca]

For it to work you need to do the following.


Setup the ftp task, add username/password, and destination folder. Don't do anything else there and close task.

Then go to the disconnected properties of the package, search for the ftp task just created, and on the "sourceFilename" property you need to enter your mainframe dataset.
This is where its tricky, as the filename alone is not enough.

you need to enter as
'mainframe_dataset_name';'';'0';

Once this is done it will work if you have access to the dataset with the username/password you entered above.

On the destination folder use one on a local drive for testing.

Once you have done the above run the task AS IS. Do not submit it as a job to the SQL Server. If it works then do whatever changes required to the destination folder.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

Hi Frederico,
I followed your instructions. The job ran without error, but the file was not transferred.

 

[fredericofonseca]

nothing else i can do then. file should be on the destination folder you placed on the ftp step, and the name will be the same as the mainframe dataset.

If no one else on your company can help you then I suggest you hire someone from around there to do that piece of work.

Alternatively, if you can supply me with vpn/vnc access to your computer i can do it remotely.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Valeda]

Thanks so much for trying. It would probably cost me my job to give you remote access. I don't think it's worth that. I will play around with it. I can't understand why it's not working. You know it has to be something very simple. That's usually the way.

Thanks again.