scheduled package works intermittantly

Last Post 15 Feb 2008 01:03 PM by svsent. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
brass
New Member
New Member

--
02 Mar 2007 10:18 AM
Single SQL Server 2000

BackGround
I have a DTS package that only has one active-x script step. This vbscript:
- opens a log(text file)
- connects to database
- iterates thru a table creating a new text file for each record logging all the way
- cleanup etc.

I have a job that calls the DTS package every 4 minutes. This job:
- calls DTS package
- calls a couple of filemanagement BAT files

Problem
I find out that txt files havn't been created for some time so I start checking the server.
- job history which goes back a little over an hour says success for each run.
- DTS text log file's last timestamp is 14 hours previous and no errors logged.
- database table which gets consumed has a build up of records, the oldest ~14 hours old.

This has happened on 3 different occasions. On the first two occasions, the whole setup starts working while I'm looking at the data. It just starts working. On this last occasion, I manually triggered the DTS package which worked flawlessly, then I manually triggered the job to flush the newly created text files to thier destinations. The system then continued on working flawlessly, and is still working flawlessly. I dont know when it decides not to work.

To many files?:
On a normal run, 0 to ~8 files get created. This morning's kickstart created 272 files, so I know it's not a matter of two many records in the database.

Does anybody have any ideas what this might be? (besides a headache!)

Average computer load: < 5% with jumps to ~15% (cpu)
brass
New Member
New Member

--
02 Mar 2007 06:37 PM
quote:

Originally posted by: russellb
1. let's see the activeX scripot code.

see below.
quote:

2. what do the batch files do?

- step 1: call DTS
- step 2: error DTS (only executed if step 1 fails) (a stored procedure call to insert a message in our alert table.)
- step 3: FTP batch file (ftp's the newly created files to another server at a remote location.)
- step 4: error ftp (like step 2, only executed if step 3 errors)
- step 5. system command 'del' to delete files
- step 6: system command to 'del' more files with different extension

quote:

3. do u have any workflow properties for on failure of task?

in the DTS? no, but I think I'm going to add one right now that just reports failure.




NOTE: If you look at function Main, the first thing that happens is function Initialize. Initialize inserts a line into the Log file. This NEVER happens.

'---------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
' vbs code from here on out.

Option Explicit

' This package process moves records from msgQueUTSout table
' to the outbox.
' To configure, just set the constants in the following lines.


'----------------------------------------------------------------------------------
' CONFIGURATION -
'--------------------------------------------------------------------------------------

' upper tier system id:
CONST kUTSID = 1

' directory locations
CONST kQUEUEPATH = "E:\PWMFTP\MRPXQUE"
CONST kOUTPATH = "E:\PWMFTP\MRPXOUT"
CONST kOUTLOGPATH = "E:\PWMLOG\OUT"


'the sqlserver network name.
CONST kSQLSERVER = "localhost"
CONST kDATABASE = "pwmdb"
CONST kDBUSER = "sa"
CONST kDBUSERPASSWORD = "xxxx"

'the log file
CONST kLOGFILE = "E:\PWMLOG\UTS-Out.log"

'what logging level do you want?
' 1 critical logging only
' 2 important and critical logging only
' 3 log most everything
' 4 log in detail
' 5 log all sql statements
CONST kLOGLEVEL = 2

'--------------------------------------------------------------------------------------
' END CONFIGURATION
'--------------------------------------------------------------------------------------

'for file opening
Const kFORREADING = 1, kFORWRITING = 2, kFORAPPENDING = 8

'set error type
Const kALERTTYPE = 5

' Global variables or object handles.
Dim oLogFile
Dim kCONNECTIONSTRING

'start the ball rolling
Main

Function Main()
On Error Resume Next

Dim myConn, rs, sSQL, alertMessage

'open log file, and initialize misc.
Initialize

'check for any errors in Initialize
' if so, alert PWM, and exit.
If Err.Number <> 0 Then
'msgBox "err != 0"
errNumber = Err.Number
errDescription = Err.Description
Err.Clear()
alertMessage = "DTS package PWM UTS-OUT: " & errNumber & ": " & errDescription

'setup the database connection
Set myConn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
myConn.Open(kCONNECTIONSTRING)

'create alert
sSQL = "EXEC pwmdb.dbo.usp_alertCreate '" & alertMessage & "', " & kALERTTYPE
rs.Open sSQL, myConn

'clean up
'rs.Close()
Set rs = Nothing
myConn.Close()
Set myConn = N
brass
New Member
New Member

--
02 Mar 2007 06:45 PM
It actually quit working for ~hour, but then started working again on its on again.
svsent
New Member
New Member

--
15 Feb 2008 01:03 PM
I experienced the same problem. We have a job scheduled to run every day. It has been running for 3 and half monts successfully except three cases. These times the job history shows success but there is no log in DTS package log and no job done by DTS package. It looks like DTS never started in these three cases. Did you managed to resolve your problem?
I could not resolve the problem in our case.
svsent
New Member
New Member

--
20 Feb 2008 11:12 AM
DTS is set to logging an execution but there is nothing in the log file for the days when DTS has failed to run.
History log shows that the step which run DTS was invoked but run only 00:00:00 instead of usual 24 min.
It looks like SQL Agent starts the package (I based it on the record in the Job history) but package never actually run because of unknown reason. The first step of the package is to call to master..xp_logevent to log the start of the package execution. Nothing in the sql log for these days neither.


Acceptable Use Policy
---