Down Load text file from FTP thro' DTS

Question

Post reply

Down Load text file from FTP thro' DTS

spdhiva

SSC-Addicted

Points: 428
More actions
April 1, 2003 at 6:11 pm

#105942

I would like to connect and download the text file from FTP thro DTS or any utility on SQL Server with out using any .BAT file.The filename on the FTP is not a static file and Dynamically they change the file name.Ex: The file name always start with the constant name Filename_ddmmyyyy.txt.
If you guys give me some idea and that would be great.

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

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Take a look at the following articles, they'll give you a start.

http://www.sqldts.com/default.aspx?6,102,231,0,1

http://www.sqldts.com/default.aspx?6,103,246,0,0

http://www.sqldts.com/default.aspx?6,101,200,0,1

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 04/01/2003 9:52:47 PM

--------------------
Colt 45 - the original point and click interface

spdhiva SSC-Addicted Points: 428 More actions · Answer 2

Phill Thanks for the help and I have no problem with the file transformation.

I need to CONNECT the FTP Server first and second, Down load the file to the network drive or Local disk and then file handling.We have to automate this process.If we use .BAT pgm, then we can't use wildcard char (Ex:filename_*.*).If you could provide any scripts which log into the FTP and download files.

Thanks

-Dhiva

tcartwright SSCertifiable Points: 5573 More actions · Answer 3

Well, I am not sure it can do wild cards, but given that you know the format of

the extra portions of the file name you could use this function I wrote and wrap

it in a VB dll to use in your code. It calls an API which is not callable from

DTS. There is no error handling as I leave that up to the developer to add their

own brand of error handling.

 

Public Function DownloadInternetFile(URL As String, _

            LocalFilename As String) As Boolean



    'INFO :http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wceurlmn/htm/cerefurldownloadtofile.asp

    

    '   EXAMPLE CALLS

    '

    '

    '    If Not CBool(Len(Dir("C:\TempDL\", vbDirectory))) Then

    '        MkDir "C:\TempDL\"

    '    End If

    '

    '    '***an http download

    '    DownloadInternetFile "http://www.msn.com", "C:\TempDL\msn.htm"

    '

    '    '***a straight ftp download

    '    DownloadInternetFile "ftp://ftp.intel.com/README", "C:\TempDL\README.txt"

    '

    '    '***an example with a login

    '    'the at sign in the url can only appear once

    '    'If the username requires an @ (for example an email address as username)

    '    'Then UrlEncode the @ = hex(asc("@")) = 40 = %40

    '

    '    'Ex. DownloadInternetFile "ftp://USERNAME%40DOMAIN:THEPASSWORD@ftp.FTPSITE.com/files/FILE", "C:\TempDL\FILE.TXT"

    '

    '    DownloadInternetFile "ftp://USERNAME:THEPASSWORD@ftp.FTPSITE.com/files/FILE", "C:\TempDL\FILE.TXT"



    Dim lngRetVal As Long

    lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)

    DownloadInternetFile = (lngRetVal = 0)

End Function

You could call this function from VBScript (given your example) :



Dim blnRet

Dim strFileName

strFileName = "Filename_" & GetDateFormat & ".txt"

blnRet = DownloadInternetFile("ftp://somewhere/" & strFileName, "c:\" & strFileName)



Private Function GetDateFormat()

'Author : phillcart

'http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=10682

GetDateFormat = Right("00" & month(Date()),2) & Right("00" & Day(Date()), 2) & Year(Date())

End Function

Tim C //Will code for food

spdhiva SSC-Addicted Points: 428 More actions · Answer 4

Hi Tim,

I try to execute the code which you have given here.I have the following questions abt this script.

This is the fn code to download the file.

HRESULT URLDownloadToFile(

LPUNKNOWN pCaller,

LPCTSTR szURL,

LPCTSTR szFileName,

DWORD dwReserved,

LPBINDSTATUSCALLBACK lpfnCB

);

Could you tell me that do i need to create a dll file or function and Where do you want me to keep this file?

When i try to execute the script in SQL Server and I get the VB compiler error.

If you give me little bit detail abt this and that would ne great.

Thanks

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

I do this many times a day.

In your DTS package, create an ActiveX script. Use the FileSystemObject to write your ftp script that you need to run that day. Then add an execute process task that runs windows built in http://ftp.exe program with and pass in the script you just wrote as the ftp script parameter.

Very simple.

Email me if you need to see some sample code.

robertd@realtechllc.com

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

tcartwright SSCertifiable Points: 5573 More actions · Answer 6

spdhiva, you need visual basic, or another compiler to compile that code into a dll, if you want I can comile that code into a dll for you and email it to you. Sorry for my long delay in responding I have been otherwise engaged.

Tim C //Will code for food