ActiveX DTS not working on Schedule

matteog

2009-07-28 13:09:22 UTC

Post by PJames
Hi,
I have an ActiveX task in my DTS (SQL 2000) which just goes and gets a
file from a network server and puts it on a network drive. Nothing
fancy. No checking for connections or validating the file arrives.
Works fine when I run it manually, either just this step or the entire
DTS package. When I put it on schedule, the DTS actually seems to work
except that the resulting file in the destination directory is 0
bytes. The file gets created on the target, so it doesn't seem like a
writing permissions issue. Any thoughts? Here is the part of the
DTS that puts the file. (By the way, I am not using the FTP task that
comes with SQL Server due to the bug in 2000 which corrupts the DTS
package if it is moved to a different server. Our separation of
duties do not allow me to put the package into production or for the
DBA to modify DTS packages..)
'*******************************************************************************************
' This script uses FTP to move a file from the transfers directory
for processing
''*******************************************************************************************
Function Main()
Dim oFso, oShell, srcPth, txtFile
srcPth = "\\xxx-xxx\someplace\corp\"
Set oFso = CreateObject("Scripting.FileSystemObject")
Set txtFile = oFso.CreateTextFile( srcPth & "ftpSend.scr", True)
'Write the Header
txtFile.writeline( "open xxx-yyy-zzz" )
txtFile.writeline( "useridhere" )
txtFile.writeline( "passwordhere" )
txtFile.writeline( "get thisfile.csv "\\xxx-xxx\someplace\corp
\" )
txtFile.writeline( "bye")
txtFile.Close
Set oShell = CreateObject("WScript.Shell")
oShell.Run "ftp.exe -i -s:\\xxx-xxx\someplace\corp\ftpSend.scr"
=========================
I have also tried creating a bat file which has the FTP in it and
running that at the oShell.Run line, but the result is the same.
Any ideas?
Thanks

Hi James,
Really sure it isn't a permission issue? As you maybe already know,
sqlserver agent runs with different credentials so this can lead me to
consider security and sharing permissions of the NT account used to
launch SQLServer.
Second consider adding a > ftplog.txt to your ftp.exe row, in order to
see how the session evolves.
Finally, if the remote FTP server is outside your Intranet/Domain,
could be that your sysadmin has protected (i wouldn't blame him for
doing this :)) the DB server (or better, the machine on which SQL
Server Agent is running) against outgoing connections.

In a DTS used to send simple text files from one internal server to an
external one every hour, I use:
- an ActiveX Script Task to generate the ftp command file (your \\xxx-
xxx\someplace\corp\ftpSend.scr)
- a bat file used to effectively launch ftp.exe and manage error level
(so i can know if the ftp session has succeeded or not).

This is part of ActiveX Script Task, someway similar to yours:

' *** FTP session parameters
strFTPserver = "serverip"
strLogin = "user"
strPwd = "pwd"

' *** file names and path used
strRootPath = "\\server\whateverpath\"
strFTPCommandFile = "ftpPut.cmd"
strFileName = "FileToPut"

' *** create ftp command file
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFTPCmdFile = fso.CreateTextFile(strRootPath &
strFTPCommandFile, true)
objFTPCmdFile.writeline( "open " & strFTPServer )
objFTPCmdFile.writeline( strLogin)
objFTPCmdFile.writeline( strPwd )
objFTPCmdFile.writeline( "put " & strFileName)
objFTPCmdFile.write( "bye")
objFTPCmdFile.Close

Then I attach an Execute Process Task in which I define statically to
execute a SendFTP.bat giving as parameter the path to the newly
created ftp command file. The SendFTP.bat file is:
FTP -d -s:%1 > %2
Type %2 >> \\S-CS-HR-0\HRO\Timbrature\exec\HISTORY.log
If Exist %2 Type %2 | Find "221 " > Nul
If Exist %2 If not errorlevel 1 If not errorlevel 2 goto 221_ok
exit 1
:221_ok
Type %2 | Find "226 " > Nul
If not errorlevel 1 If not errorlevel 2 goto 226_ok
exit 1
:226_ok
Type %2 | Find "530 " > Nul
If errorlevel 1 If not errorlevel 2 goto 530_ok
exit 1
:530_ok
Type %2 | Find "550 " > Nul
If errorlevel 1 If not errorlevel 2 goto 550_ok
exit 1
:550_ok
exit 0

I can then manage different error levels resulting from the ftp error
codes: combined with DTS workflow I can let package behave
differently, depending on FTP session results.

HTH,
M.