VBScript and Scheduled Tasks

[dodgyone]

I have created a series of VBScripts to run performance monitoring and input the data into SQL Server. It all works fine when I run each in turn manually. Whenever I schedule them to run at the same time then it fails. Have I got to stagger these in some way to get it working? Surely it should work and run it's own Wscript.exe process for each scheduled task until completed?

In simplistic terms the script below does this:
1. Start the selected Performance Monitor (PerfMon)
2. Sleep for 1 hour
3. Stop the selected PerfMon
4. Wait for 30 seconds (give PerfMon time to do it's thing)
5. Update and edit the outputted data from PerfMon
6. Run the SQL Server DTS transformation task
7. Copy the outputted file to an archive area

Here is an example of my code. The only alteration made in each script is in the setting of variables (e.g. 'itax-dm3' changed to 'itax-dm5' and so on):

'---------
'Variables
'---------
Dim objFSO, DataFile, CopyDest, DTSSvr, DTSPkg, intPausePeriod, strLogFileToUse
Dim intNowDay, intNowMonth, intNowYear, intHour, intMin, intNowDate 
'create the formatted date
intNowDay = Left(Now(), 2)
intNowMonth = Mid(Now(), 4, 2)
intNowYear = Mid(Now(), 7, 4)
intHour = Mid(Now(), 12, 2)
intMin = Mid(Now(), 15, 2)
intNowDate = intNowYear & intNowMonth & intNowDay & " " & intHour & intMin
'other variables
intPausePeriod = 3600000 '(e.g. 1000 = 1 second, 900000 = 1/4 hour, 1800000 = 1/2 hour, 3600000 = 1 hour)
strLogFileToUse = """ITAX-DM3"""
DataFile = "C:\PerfLogs\ITAX-DM3.tsv"
strSearchString = """ """
strReplaceString = """0"""
CopyDest = "C:\PerfLogs\Log Record\ITAX-DM3\" & intNowDate & ".txt"
DTSSvr = "itax-sql2"
DTSPkg = "PerfMon ITAX-DM3"


Set objFSO = CreateObject("Scripting.FileSystemObject")

'-----------------------------------------------------------
'Run the performance log task for a specified period of time
'-----------------------------------------------------------
Dim objShell
Set objShell = WScript.CreateObject( "WScript.Shell" )
'start the performance logging
objShell.Run("logman start " & strLogFileToUse)
'pause the code so the logging can happen
WScript.Sleep intPausePeriod
'stop the performance logging
objShell.Run("logman stop " & strLogFileToUse)
Set objShell = Nothing
'if you don't pause here for a while then the log file code below will fail!
WScript.Sleep 30000 '30 seconds

'--------------------
'Performance Log File
'--------------------
Dim objFile, objFileCopy, intLineCount, strNewContents, strLine, strLineNew, strText, strNewText
'does the log data file exist?
If objFSO.FileExists(DataFile) = True Then
	
	'Search for empty/blank results in the dataset and enter a zero in its place
	Set objFile = objFSO.OpenTextFile(DataFile, 1)
	strText = objFile.ReadAll
	objFile.Close
	strNewText = Replace(strText, strSearchString, strReplaceString)
	Set objFile = objFSO.OpenTextFile(DataFile, 2)
	objFile.WriteLine strNewText
	objFile.Close
	
	'general tidy up and update of the file
	intLineCount = 0
	Set objFile = objFSO.OpenTextFile(DataFile, 1)
	Do Until objFile.AtEndOfStream
		intLineCount = intLineCount + 1
		strLine = objFile.Readline
		'update the line 
		If intLineCount <> 1 And left(strLine, 1) = Chr(34) Then
			'take off the milliseconds
			strLineNew = Left(strLine, 20)
			strLineNew = strLineNew & Mid(strLine, 25)
			'convert the string date to DDMMYYYY so the SQL Server DTS transformation works later on below
			strDay = Mid(strLineNew, 5, 2)
			strMonth = 	Mid(strLineNew, 2, 2)
			strLineNew = Chr(34) & strDay & "/" & strMonth & Mid(strLineNew, 7)
		Else
			strLineNew = strLine
		End If
		'ignore blank/empty lines and the second line which has empty data in it
		If intLineCount <> 2 And Left(strLineNew, 1) <> "" Then
			strNewContents = strNewContents & strLineNew & vbCrLf 
		End If
	Loop
	Set objFile = objFSO.OpenTextFile(DataFile, 2)
	objFile.Write strNewContents
	objFile.Close
	
	'copy the newly created file and date it
	Set objFileCopy = objFSO.GetFile(DataFile)
	objFileCopy.Copy (CopyDest)
	Set objFileCopy = Nothing

Else
	
	Set objFileCopy = Nothing
	WScript.Quit

End If

'----------------------
'SQL Server DTS Package
'----------------------
Dim objDTSPackage, DTSStatus, DTSStep
'run the DTS package
Set objDTSPackage = CreateObject("dts.package")
objDTSPackage.LoadFromSQLServer DTSSvr, , , 256, , , , DTSPkg
objDTSPackage.Execute()
'close the DTS package
objDTSPackage.Uninitialize()
Set objDTSPackage = Nothing

'---------------------------------
'Delete the original log data file
'---------------------------------
objFSO.DeleteFile DataFile

'Close the script
Set objFSO = Nothing
WScript.Quit

Does anybody have any ideas or suggestions? Thanks...

 

[dodgyone]

I should of added that the scheduled task runs the performance monitoring part of the script but seems to stop when it reaches the update and edit of the outputted performance monitoring file:

'--------------------
'Performance Log File
'--------------------
Dim objFile, objFileCopy, intLineCount, strNewContents, strLine, strLineNew, strText, strNewText
'does the log data file exist?
If objFSO.FileExists(DataFile) = True Then

 

[dodgyone]

Does anybody have any ideas? The WScript.Sleep seems to be the cause of the problem. It is set to sleep for 3600000 milliseconds and it all works fine when I run it manually. Surely I can use WScript.Sleep in a scheduled task?

 

[theniteowl]

I am only a tinkerer with VBS myself but here are my thoughts.
You can test that your script is still running though asleep by pulling up task manager and looking for it in the running applications and/or processes and then watch to see that it actually closes itself down after the timeout has completed. I have seen scripts before that hang up and never close out.

Rather than putting the script to sleep perhaps you can set your other script to exit with a return code and have the first script watch for a successful return code before continuing.

You could combine the scripts into one and have only one execution that handles the delay between execution of it's sub parts.


At my age I still learn something new every day, but I forget two others.

 

[tsuji]

>'create the formatted date
>intNowDay = Left(Now(), 2)
>intNowMonth = Mid(Now(), 4, 2)
>intNowYear = Mid(Now(), 7, 4)
>intHour = Mid(Now(), 12, 2)
>intMin = Mid(Now(), 15, 2)
>intNowDate = intNowYear & intNowMonth & intNowDay & " " & intHour & intMin

Why are you at all doing that? Have you checked what it comes out as intNowDate? It may well contain illegal character for a file name!?

I would suggest at least a variant of this?
[tt]
dim dt
dt=now
intNowDay = right("00" & day(dt),2)
intNowMonth = right("00" & month(dt),2)
intNowYear = year(dt)
intHour = right("00" & hour(dt),2)
intMin = right("00" & minute(dt),2)
intNowDate = intNowYear & intNowMonth & intNowDay & " " & intHour & intMin
[/tt]

 

[dodgyone]

The date formatting works fine and has no bearing on the other code than to name the file that is archived. Cheers for the advice though.

I tried staggering the scheduled tasks every 2 minutes and it now works as it should do. WScript has its own process for each task so I cannot see why it fails when they all run at once. Perhaps the Performance Monitor cannot handle the multiple requests all in one go?!!!!

 

[tsuji]

Well then, that script relies much on chance.

 

[dodgyone]

But there are 4 scripts all with the same code and the only alteration has been to change the server name variables. Surely it shouldn't rely on chance if it's doing the same thing over and over?!!

 

[tsuji]

...so it must not rely on chance. Just for curiosity, what is the intNowDate do you get for the present for instance?

 

[dodgyone]

20080312 1212

...which later on becomes an archive file name (20080312 1212.txt)

 

[tsuji]

Then you have to make sure whoever run your script have the os date time format exactly as your localized system setting. That's all I would say. Thanks for your feedback.