asked on

Active-X Task Not Working Correctly on the Server

Any help would be appreciated:

The following code is in an ActiveX task in a DTS package. It works with no probelms when executed from a user's machine, but when I try to execute it as a job on the server, I get different errors.

First of all, there is an Execute SQL task in front of it that returns a RowSet into a global variable (RSTables). There are 9 rows of data in it. The object of the task is to use the data returned to build a .CMD file along with an FTP script file, then use the Run method of the WScript.Shell object to execute each .CMD file, thus FTP'ing 9 files. Parsing the log file created by the FTP command, I then determine if the process was successful. If so, I call a stored procedure to update specific columns in a table on the server.

If I run the code as-is, it 'successfully' FTPs the frist file, but does not update the underlying table to indicate success and errors. The package errors at the point I have indicated in the code below and only one file gets transferred (although, because of the error, indications are that it did not get transferred) . I have attempted to put an ON ERROR RESUME NEXT statement just before that point, in which case all command, script and log files get created, all files get transferred, but all logging indicates the package failed. In fact, nothing gets updated on the server. When the package is run as a job, with the ON ERROR RESUME NEXT statement, the job history log has the following:

Executed as user: EQ\$DBMTeamSQL.
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_GetTablesRS
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_GetTablesRS
DTSRun OnStart: DTSStep_DTSActiveScriptTask_FTPFiles
DTSRun OnError: DTSStep_DTSActiveScriptTask_FTPFiles, Error = -2147220421 (8004043B)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0)
Error string: The task reported failure on execution.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 1100
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_FTPFiles
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
Process Exit Code 1. The step failed.

When run without the ON ERROR RESUME NEXT the following error is reported (Line 213 is: If fso.GetFile(DestinationFile).Size = 0 Then):
Executed as user: EQ\$DBMTeamSQL. ...ecuting...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_GetTablesRS
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_GetTablesRS
DTSRun OnStart: DTSStep_DTSActiveScriptTask_FTPFiles
DTSRun OnError: DTSStep_DTSActiveScriptTask_FTPFiles, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: File not found
Error on Line 213
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 4500
Error Detail Records: Error: -2147220482 (800403FE);
Provider Error: 0 (0) Error string:
Error Code: 0 Error Source= Microsoft VBScript runtime error
Error Description: File not found
Error on Line 213
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScrip. The step failed.

Option Explicit
 
' FSO Constants
Const WindowsFolder = 0
Const SystemFolder = 1
Const TempFolder = 2
Const ForReading = 1
Const ForWriting = 2
Const ForAppend = 8
Const TristateUseDefault = -2
Const TriStateTrue = -1	' For reading unicode file, which is what import file is.
Const TriStateFalse = 0	' For reading ASCII (default)
 
' ADO Object Parameter Enumerations
'---- ExecuteOptionEnum Values ----
Const adRunAsync = &H00000010
Const adAsyncExecute = &H00000010
Const adAsyncFetch = &H00000020
Const adAsyncFetchNonBlocking = &H00000040
Const adExecuteNoRecords = &H00000080
 
'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004
 
'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200
 
'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139
 
Function Main()
	Dim oRS, ImportGroup, gdg, date, FileType, sourceDSN, path, destinationFilename, rc
	Dim adoConn, adoCommand, sqlText, connString, adoParam, msg, fso, step
 
	Set adoCommand = CreateObject("ADODB.Command")
	Set adoConn = CreateObject("ADODB.Connection") 
	connString = "Provider=SQLOLEDB;Trusted_Connection=yes; Server=kccrmp12; Initial Catalog=DishDDE; Integrated Security=SSPI;" 
 
	adoConn.ConnectionString = connString
	adoConn.Open
	adoCommand.ActiveConnection = adoConn.ConnectionString
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "ImportGroup"
	adoParam.Type = adInteger
	adoParam.Direction = adParamInput
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "GDG"
	adoParam.Type = adChar
	adoParam.Size = 4
	adoParam.Direction = adParamInput
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set oRS = DTSGlobalVariables("RSTables").Value
 
	Main = DTSTaskExecResult_Success
 
	oRS.MoveFirst
	While Not oRS.EOF
		ImportGroup = oRS.Fields(0).Value
		FileType = oRS.Fields(1).Value
		path = oRS.Fields(2).Value
		gdg = Right("0000" & CStr(CInt(oRS.Fields(3).Value) + 1), 4)
		sourceDSN = Replace(oRS.Fields(5).Value, "*", gdg)
		destinationFilename = Replace(oRS.Fields(6).Value, "*", gdg)
		rc = BuildFTPCommand(FileType, sourceDSN, path & destinationFilename)
		If rc = 0 Then 
			SqlText = "UpdateImportGroupStats"
			adoCommand.CommandText = SqlText
			adoCommand.CommandType = adCmdStoredProc
			adoCommand.Parameters("ImportGroup").Value = ImportGroup
			adoCommand.Parameters("GDG").Value = gdg
			adoCommand.Execute, , adExecuteNoRecords
			msg = sourceDSN & " to " &  path & destinationFilename
			step = "Transferred successfully."
			WriteLogMessage msg, step, 0, 0
		Else 
			msg = sourceDSN & " to " &  path & destinationFilename
			step = "An error occurred while attempting to transfer this file."
			WriteLogMessage msg, step, 1, rc
			If fso.FileExists(path & destinationFilename) Then
				fso.DeleteFile path & destinationFilename
			End If
			Main = DTSTaskExecResult_Failure
		End If
		oRS.MoveNext
	Wend
 
	Set oRS = Nothing
 
	adoConn.Close
	Set adoCommand = Nothing
	Set adoConn = Nothing
End Function
 
Function BuildFTPCommand(FileType, SourceFile, DestinationFile)
	Dim fso, sfile, UserID, Pswd, Server, Executable, rc, CommandFile
	Dim WinDir, TempDir, ScriptFile, idx, LogFile, ctr
	Dim CommandFileStream, CommandFileObject, ScriptFileStream, ScriptFileObject
 
	Set fso = CreateObject("Scripting.FileSystemObject")
 
	Set WinDir = fso.GetSpecialFolder(SystemFolder)
	If Right(WinDir, 1) <> "\" Then WinDir = WinDIr & "\"
 
	Set TempDir = fso.GetSpecialFolder(TempFolder)
	If Right(TempDir, 1) <> "\" Then TempDir = TempDIr & "\"
 
	Executable = WinDir & "ftp.exe -n -s:"
 
	UserID = DTSGlobalVariables("UserID").Value
	Pswd = DTSGlobalVariables("Password").Value
	Server = DTSGlobalVariables("SourceServer").Value
 
	LogFile = TempDir & FileType & ".log"
	ScriptFile = TempDir & FileType & ".ftp"
	CommandFile = TempDir & FileType & ".cmd"
	DTSGlobalVariables("LastCmdFile").Value = CommandFile
 
	If fso.FileExists(CommandFile) Then
		fso.DeleteFile CommandFile
	End If
	If fso.FileExists(LogFile) Then
		fso.DeleteFile LogFile
	End If
	If fso.FileExists(ScriptFile) Then
		fso.DeleteFile ScriptFile
	End If
 
	fso.CreateTextFile ScriptFile
	Set ScriptFileObject = fso.GetFile(ScriptFile)
	Set ScriptFileStream = ScriptFileObject.OpenAsTextStream(ForWriting, TriStateFalse)
 
	ScriptFileStream.WriteLine "USER " & UserID
	ScriptFileStream.WriteLine Pswd
	ScriptFileStream.WriteLine "GET '" & SourceFile & "' " & Chr(34) & DestinationFile & Chr(34)
	ScriptFileStream.WriteLine "BYE"
	ScriptFileStream.Close
 
	Set ScriptFileStream = Nothing
	Set ScriptFileObject = Nothing
 
	fso.CreateTextFile CommandFile
	Set CommandFileObject = fso.GetFile(CommandFile)
	Set CommandFileStream = CommandFileObject.OpenAsTextStream(ForWriting, TriStateFalse)
	CommandFileStream.WriteLine Executable & ScriptFile & " " & Server & " > " & LogFile
	CommandFileStream.WriteLine "EXIT"
 
	CommandFileStream.Close
 
	Set CommandFileStream = Nothing
	Set CommandFileObject = Nothing
 
	ctr = 0
	Do 
		ctr = ctr + 1
		rc = ExecuteShellProgram(CommandFile)
		BuildFTPCommand = CheckLog(LogFile)
	Loop While ((BuildFTPCommand = 3) And (ctr < 11))
' ******************** This is where the error occurs ********************
	If BuildFTPCommand = 0 Then
		If fso.GetFile(DestinationFile).Size = 0 Then
			BuildFTPCommand = 6
		End If
	End If
End Function
 
Function ExecuteShellProgram(CommandFileName)
	Dim poShell, rc
 
	Set poShell = CreateObject("WScript.Shell")
	rc = poShell.Run("cmd /k " & CommandFileName, 2, 1)
End Function
 
'*************************************************************************************************
' Check the log file for status of transfer.
' Return codes:
'    0 - Success
'    1 - Default value-indicates possible error occurred in the function
'    2 - Host file not found
'    3 - Apparent timeout attempting to retrieve migrated file
'    4 - Transfer Error
'    5 - Not connected. - Unable to connect to mainframe.
'    6 - Zero length file returned (checked one level up in calling function)
'    7 - File is allocated to another job and cannot be accessed
'    8 - Invalid password provided.
'    9 - Log file not found
'*************************************************************************************************
Function CheckLog(LogFile)
	Dim fso, log, LogData
 
	CheckLog = 1	'default
 
	Set fso = CreateObject("Scripting.FileSystemObject")
 
	If fso.FileExists(LogFile) Then
		Set log = fso.OpenTextFile(LogFile, ForReading)
		Do Until log.AtEndOfStream
			LogData = log.ReadLine
			Select Case Left(LogData, 3)
				Case "125" 
					If Instr(1, LogData, "Waiting for recall of data set") > 0 Then
						'Read next line and see if it is a logout
						LogData = log.ReadLine
						If Instr(1, LogData, "BYE") > 0 Then
							CheckLog = 3	' timeout
							Exit Do
						ElseIf Instr(1, LogData, "Error recalling data set") > 0 Then
							If Instr(1, LogData, "(rc=2)") > 0 Then
								CheckLog = 7	' dataset allocated to another job
								Exit Do
							End If
						End If
					ElseIf Instr(1, LogData, "Error recalling data set") > 0 Then
						If Instr(1, LogData, "(rc=2)") > 0 Then
							CheckLog = 7	' dataset allocated to another job
							Exit Do
						End If
					End If
				Case "451" 
					If Instr(1, LogData, "Transfer aborted: send error", 1) > 0 Then
						CheckLog = 4	' Transfer Error
						Exit Do
					End If
				Case "250"
					If InStr(1, LogData, "Transfer completed successfully", 1) > 0 Then
						CheckLog = 0	' success
						Exit Do
					End If
				Case "530"
					If InStr(1, LogData, "PASS command failed", 1) > 0 Then
						CheckLog = 8	' Password invalid
						Exit Do
					End If
				Case "550"
					If InStr(1, LogData, "not found", 1) > 0 Then
						CheckLog = 2	' host file not found
						Exit Do
					End If
				Case "Not"
					If LogData = "Not connected." Then
						CheckLog = 5	' unable to log in
						Exit Do
					End If
				Case Else
			End Select
		Loop
		log.Close
	Else
		CheckLog = 9	' log file not found. Assume no FTP
	End If
	Set log = Nothing
	Set fso = Nothing
End Function
 
Sub WriteLogMessage(Message, Step, IsError, ErrorCode)
	Dim adoConn, adoCommand, connString, adoParam
 
	Set adoConn = CreateObject("ADODB.Connection") 
	Set adoCommand = CreateObject("ADODB.Command")
	connString = "Provider=SQLOLEDB;Trusted_Connection=yes; Server=kccrmp12; Initial Catalog=DishDDE; Integrated Security=SSPI;" 
 
	adoConn.ConnectionString = connString
	adoConn.Open
	adoCommand.ActiveConnection = adoConn.ConnectionString
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "ProcName"
	adoParam.Type = adVarChar
	adoParam.Direction = adParamInput
	adoParam.Size = 128
	adoParam.Value = "DTS Package: FTP Dish DDE Files"
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "Message"
	adoParam.Type = adVarChar
	adoParam.Direction = adParamInput
	adoParam.Size = 4000
	adoParam.Value = Message
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "IsError"
	adoParam.Type = adBoolean
	adoParam.Direction = adParamInput
	adoParam.Value = IsError
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "ProcStep"
	adoParam.Type = adVarChar
	adoParam.Size = 200
	adoParam.Direction = adParamInput
	adoParam.Value = Step
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "SPID"
	adoParam.Type = adInteger
	adoParam.Direction = adParamInput
	adoParam.Value = 0
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "CPUTme"
	adoParam.Type = adBigInt
	adoParam.Direction = adParamInput
	adoParam.Value = 0
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "IOCount"
	adoParam.Type = adBigInt
	adoParam.Direction = adParamInput
	adoParam.Value = 0
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "StatementErrorCode"
	adoParam.Type = adInteger
	adoParam.Direction = adParamInput
	adoParam.Value = ErrorCode
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	Set adoParam = CreateObject("ADODB.Parameter")
	adoParam.Name = "RunTimeMilliseconds"
	adoParam.Type = adBigInt
	adoParam.Direction = adParamInput
	adoParam.Value = 0
	adoCommand.Parameters.Append adoParam
	Set adoParam = Nothing
 
	adoCommand.CommandText = "CreateLogMessage"
	adoCommand.CommandType = adCmdStoredProc
	adoCommand.Execute, , adExecuteNoRecords
 
	adoConn.Close
	Set adoCommand = Nothing
	Set adoConn = Nothing
End Sub

Open in new window

Anthony Perkins

>>The following code is in an ActiveX task in a DTS package. It works with no probelms when executed from a user's machine, but when I try to execute it as a job on the server, I get different errors.<<
That tells me that the startup account used by the SQL Server Agent service does not have permissions to all the resources needed for your DTS package.

>> I have attempted to put an ON ERROR RESUME NEXT statement just before that point<<
That is a very bad idea. It is equivalent of saying, if there are any errors I don't care, ignore them.

D B

ASKER

First of all, the ON ERROR RESUME NEXT was just for troubleshooting. I added it to see what would happen if I ignored errors. I do not normally use it.

Secondly, I would question the statement that the SA server account does not have the necessary permissions. Since the first file is successfully transferred, and I am not having any problems with the FSO object execpt on the line referenced in the error statement (If fso.GetFile(DestinationFile).Size = 0 Then...). It does not make sense to me that only cetain properties and methods of the FSO would work under a certain account and others wouldn't.

Anthony Perkins

>>Secondly, I would question the statement that the SA server account does not have the necessary permissions.<<
I suspect there is some confusion here. I am talking about the Windows Account used for the SQL Server Agent service. SA is a SQL Server account and not a Windows account.

>>It does not make sense to me that only cetain properties and methods of the FSO would work under a certain account and others wouldn't. <<
If you think about it in the above context it makes all the sense in the world and we see this over and over again. For example, whoever installed SQL Server inadvertently left the Local system account as the startup account for the SQL Server Agent service, that would mean that when run as a scheduled job you would not have access to any resources beyond the server.

Anthony Perkins

>>First of all, the ON ERROR RESUME NEXT was just for troubleshooting.<<
As you must have realized, that is not very advantageous. A better approach would be to enable just-in-time debugging and then you can see which exact line caused the error and the actual cause.

On a somewhat related note, I have no idea why you are writing all that ActiveX code when you could have written it as a simple Execute SQL Task and Data Transformation Task, but the bottom line is that if you have to do it this way, you will have to ensure that in addition to all I mentioned before the startup account for the SQL Server Agent service will also have to have permissions to execute the CreateLogMessage Stored Procedure.

D B

ASKER

Some reasons for using ActiveX are to:
1) be able to read the log file created by the FTP command to determine the outcome
2) be able to read the actual file that was sent to us. On occasion, the partner side 'hiccups' and all we get is one record, which is a header record. I want to determine that at the time I FTP the file.

We are pulling generated datasets from a mainframe, and keeping track of the last successful generation pulled for each file. Thus this is driven heavily by a control table. Some of the things I am doing in the script are not easily available in T-SQL.

I might also mention that all users are set up as administrators on this server (don't yell at the messenger-I'm just a lowly contractor doing what I'm told), and by default, no permissions are set on any objects, so any account should have access to any objects.

How do I use the JIT debugger? I've never used it before and wasn't even aware that there was one. However, the error message indicated, as I stated before, that the error is occuring on the line:

If fso.GetFile(DestinationFile).Size = 0 Then

indicating to me that the fso.GetFile() method is the cause of the error (file not found), which does not make since, because if the file was not created, then the BuildFTPCommand() function would not have returned 0.

Anthony Perkins

There is nothing to stop you doing all the FTP tasks using an ActiveX Script Task. I was referring specifically to your use of ADO to access your SQL Server this is best handled through and Execute SQL Task. Something else that would help would be if you split up that script into multiple tasks. This would make future debugging a breeze. Well, let's just say a little easier.

If you are an administrator (if you are not, find one), do the following from Enterprise Manager:
1. Click on the Management object for the server.
2. Click on SQL Server Agent.
3. Right-click and select Properties.
4. Click on the General tab.
6. Check the Service startup account.

If it is:
A. Local System account that is a problem.
B. This account has an account that does not have sufficient permissions that is a problem.
C. None of the above: I have no idea.

>>How do I use the JIT debugger?<<
1. Select Data Transformation Services
2. Right click on Properties.
3. Check the box that reads "Turn on just-in-time debugging"

NOTE: This will not help you in this case, as you have stated the problem only occurs when the DTS Package runs as a service.

nmcdermaid

FYI, its possible to read text files (i.e FTP log and source file which may only have one header) into SQL Server. You can use a
INSERT INTO HoldingTable (FileLines)
xp_cmdshell 'TYPE YourFile.TXT' command
or use BULK INSERT
to import data into tables. This can be helpful as it lets you keep a history of all logs in a single table, and tag them with information, timings etc. But you may be more comfortable doing this in ActiveX anyway. Horses for courses! Good luck.

D B

ASKER

Okay, I have logged directly onto the server and ran the created command files. As with the cases above, everything appears to run properly, and the logs indicate the files were transferred but no files were created. I then changed the command file to FTP to a local drive on the server, thinking maybe it had something to do with using a UNC mapping. Exact same results. Everything APPEARED to work properly but no files were transferred.

Since I was on the server, I added a PAUSE to the end of the command file.
I was getting the following message:
Rename Temp File calloc:No such process

I have no idea what it means. I've searched Google and unable to find any information. I am using the vanilla FTP.EXE that comes with Windows. The account is an Admin account, there are no quotas set, and I am not a C programmer, which I believe calloc has something to do with C.

Any ideas?

D B

ASKER

Okay, my bad. I had the wrong path in the local file transfer for the destination. However, after correcting it, I now get the error:
Rename Temp File calloc:File exists

with the same results, no file gets transferred, although FTP indicates it was.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

I do not think we are off topic at all. The problem was that I could not FTP files from an ActiveX script running as a service on the server, but would run properly if I executed the package on my local machine. That has always been the problem and I've supplied all the code. Once I was able to log onto the server and capture the error message, I was able to get to a deeper level of what the problem was. I don't see how that is off topic.

As it turns out, the information provided in the article applies completely to my problem. To check it out, I moved the .cmd files to the drive I am attempting to download from and the FTP works. My solution is to either get our DBA to install the hotfix, or create the .cmd file on the drive referenced in the UNC, or leave it on a local drive on the server. That fixes the problem.

Fixing the problem was the topic, and the information provided in the KB article provides me with a way to fix it. That is not off topic. Thanks for the help.

D B

ASKER

Thanks!

Anthony Perkins

Fair enough.