FTP Pull from Unix box

Question

FTP Pull from Unix box

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
July 14, 2003 at 7:43 am

#357934

What is the best way to automate the pulling of text files from a unix box (files created by Oracle).
Searching I found a script on this site to push a file, but not to pull.
I also read about using DTS in Help, and managed to logon on to our Unix box using Internet Source but was not able to drill down below the top level.
Currently I am doing the above in Access using automation with ws_ftp software. This works fine but I would prefer to eliminate Access and if possible the third party software and do the whole thing in SQL Server.
By the way we are not allowed to link to our Oracle core systems hence this rounadbout route of running automated SQLs in Oracle to create the text files.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

planet115 Hall of Fame Points: 3181 More actions · Answer 1

You may be able to do this direct from SQL Server (I don't know how but I don't know much).

If I was doing this, I would write a service application on the windows box to ftp get the file and then trigger whatever import or whatever job you want.

Hopefully someone has a simpler SQL Server only solution though ...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi Stefan,

quote:
What is the best way to automate the pulling of text files from a unix box (files created by Oracle).
Searching I found a script on this site to push a file, but not to pull.
By the way we are not allowed to link to our Oracle core systems hence this rounadbout route of running automated SQLs in Oracle to create the text files.

since you are not allowed linking to Oracle, and not using something like http://www.unixodbc.org ,I guess it is best to use Samba to place files on windows system and then use a DTS to import

Cheers,

Frank

Well, when using Samba you should be able to read from shared unix drives

Edited by - a5xo3z1 on 07/14/2003 08:08:28 AM

Edited by - a5xo3z1 on 07/14/2003 08:49:35 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 3

'Samba' is a new one on me. I found the web site but could not really see what it does. Is it going to offer any benefits over what I am currently doing:

Currently using 'QMASTER' a large number of SQLS are run on our Oracle Unix box overnight 3 times per month. The last file being a tiny dummy file.

The Access app on the PC is started by the Windows Scheduler and runs through the files in sequence starting with the dummy file attempting to download them using Automation with WS_FTP.

If the file downloads successfully, the next file is downloaded, otherwise the App waits for 15 minutes and tries again. The files are then uploaded into Access and processed before creating output files which are zipped and placed on an Intranet Server.

Would also appreciate advice whether the code at following link might resolve my problem, it looks promising but I am very new to SQL Server so would appreciate advice:

Using FTP in Transact-SQL http://www.sqlteam.com/item.asp?ItemID=6002

Edited by - stefanj on 07/15/2003 02:57:54 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Stefan,

quote:
'Samba' is a new one on me. I found the web site but could not really see what it does. Is it going to offer any benefits over what I am currently doing:

when you have found http://www.samba.org you're at the No.1 source for it.

But because this is new to you I guess it will not give you any benefits. Samba is too complex to just easy install. I think it should become part of your IT strategy to be of any benefit.

What it does?

The following is taken from the above website

Samba is a file and print server for Windows-based clients using TCP/IP as the underlying transport protocol. In fact, it can support any SMB/CIFS-enabled client. One of Samba's big strengths is that you can use it to blend your mix of Windows and Linux machines together without requiring a separate Windows NT/2000/2003 Server. Samba is actively being developed by a global team of about 30 active programmers and was originally developed by Andrew Tridgell

quote:
Currently using 'QMASTER' a large number of SQLS are run on our Oracle Unix box overnight 3 times per month. The last file being a tiny dummy file.

Sambe would enable to get directly hands from Windows on these files without FTP.

quote:
The Access app on the PC is started by the Windows Scheduler and runs through the files in sequence starting with the dummy file attempting to download them using Automation with WS_FTP.
If the file downloads successfully, the next file is downloaded, otherwise the App waits for 15 minutes and tries again. The files are then uploaded into Access and processed before creating output files which are zipped and placed on an Intranet Server.
Would also appreciate advice whether the code at following link might resolve my problem, it looks promising but I am very new to SQL Server so would appreciate advice:

I haven't dealt with FTP and SQL Server. I guess someone else here has. But until then, if your solution works, why change it?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

planet115 Hall of Fame Points: 3181 More actions · Answer 5

It looks like the code in that link could definitely be modified to do what you want.

However, working with Win APIs from a stored procedure might not be the easiest environment for debugging etc. If you are not already familiar with WinInet, I'ld recommend getting an overview of the basics here, before leaping into this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wininet/wininet/about_wininet.asp?frame=true

and if possible, maybe practising with the API in VB or C or something, just so you are confident that the methods actually work. Some samples are here

http://www.vbip.com/wininet/index.asp

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q175/1/79.asp&NoWebContent=1

You still have the option to write your FTP-pull + SQL Upload code in VB/C++ and either:

-- Schedule it as a job with windows scheduler (as you did before)

-- Run it as a service

-- Have SQL Server execute the program on a schedule (See "jobs" and "xp_cmdshell" in BOL)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hello Stefan,

quote:
By the way we are not allowed to link to our Oracle core systems hence this rounadbout route of running automated SQLs in Oracle to create the text files.

finally I found the link I've thought of

http://www.algonet.se/~sommar/mssqlperl/unix.html

Might be of any help?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]