September 2, 2004 at 8:06 pm
I have to download a file from remote server using http://FTP. The problem with DTS FTP task is that it succeeds even if file is not on server OR username and password are incorrect OR URL is incorrect. I want two things to check before I use DTS FTP task.
1) To check if connection can be established to remote server
2) To check if required file is in the specified directory
If some one has some idea of how this could be achieve using ActiveX script or SQL task, please help me.
September 6, 2004 at 8:00 am
This was removed by the editor as SPAM
September 7, 2004 at 5:42 am
Take a look at the SQLDTSFTP Custom FTP task at http://www.sqldts.com/default.aspx?272
I haven't completely reviewed all the features, but it does provide a more complete interface to FTP. It may resolve at least some of your issues.
September 8, 2004 at 1:05 pm
I finally used ActiveX script to achieve my goals.
Function Main()
Dim remoteURL, username, password, transferMode, localDir, remoteDir, remoteFile
Dim fso, ftpscrfileName, ftpscrfileDir, scriptscr, ftpscrfile
Dim ReturnCode, Ouput
Set fso = CreateObject("Scripting.FileSystemObject")
ftpscrfileDir = "C:\"
ftpscrfileName = ftpscrfileDir & "file.txt"
If not fso.FileExists( ftpscrfileName ) Then
fso.CreateTextFile( ftpscrfileName )
End If
Set ftpscrfile = fso.OpenTextFile(ftpscrfileName, 2, True)
remoteURL = "remote.server.com"
username = "abc"
password = "xyz"
transferMode = "bin" '-------asc (ascii) /bin (binary)
localDir = "C:\"
remoteDir = "CRU/" 'use "" if no directory
remoteFile = "afile.xls"
scriptscr = "open "& remoteURL & vbcrlf &_
username & vbcrlf &_
password & vbcrlf &_
transferMode &vbcrlf &_
"lcd "& localDir & vbcrlf &_
"GET "& remoteDir &""& remoteFile & vbcrlf &_
"bye"
ftpscrfile.write scriptscr
ftpscrfile.close
set oShell = CreateObject("wscript.shell")
Set oExec = oShell.Exec("ftp -s:"& ftpscrfileName)
set Output = oExec.stdOut
strOutput = Output.ReadAll()
if InStr(strOutput, "Unknown host") > 0 Then
MsgBox "Unknown host: "&remoteURL
'Event notification goes here
Main = DTSTaskExecResult_Failure
ElseIf InStr(strOutput, "No such file or directory") > 0 Then
MsgBox remoteDir & "" & remoteFile &": No such file or directory"
'Event Notification goes here
Main = DTSTaskExecResult_Failure
Else
If Not oExec.StdErr.AtEndOfStream Then
ReturnCode = "STDERR: " + oExec.StdErr.ReadAll
Else
ReturnCode = -1
End If
If -1 = ReturnCode Then
Main = DTSTaskExecResult_Success
Else
MsgBox ReturnCode
'Event notification goes here
Main = DTSTaskExecResult_Failure
End If
End If
End Function
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply