April 4, 2002 at 4:35 pm
I have an active script task in dts that works in W2K, but does not work in Win nt 4.0.
The purpose of the vbscript is to rename the output from a previous transformation step into a file name format that contains a date and time - basically my file name will be changed from something like name.txt to XXX111_20020404_101314
I'm using the exact same file names and directory structures on both the nt and the w2k machines, and I have verified the permissions on the files and directories.
I'm using the FileSystemObject and basically my code is:
If fso.FileExists(strMoveFrom) Then
fso.MoveFile strMoveFrom, strMoveTo
I have displayed the values in both the strMoveFrom and strMoveTo variables, and know they are correct, and the code works correctly on two other machines (both w2k). I also tried using MsbBox Err.Description after the MoveFile statement and the error displayed was 'object required'.
In my research I have found that the Windows Scripting Host is not delivered with Win NT 4.0 as it is with W2K, is it delivered with SQL Server 7.0? The vbscript parses correctly in nt and runs to completion, it just never moves the file.
Any suggestions?
April 4, 2002 at 7:32 pm
No but I beleive IE 5+ has it, plus there is a seperate download available somewhere on the sight.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 7:52 pm
April 4, 2002 at 8:34 pm
It doesn't seem like WSH is the problem because IE 5 is installed. Any other suggestions?
April 4, 2002 at 8:57 pm
Does it work ok if you run it as a plain .VBS file from Windows rather than via DTS?
Andy
April 4, 2002 at 9:07 pm
Also can you post a snippet of the code to give us a better idea of what happens and we can try to duplicate and resolve on our test environments.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 12:56 pm
Here is the code. Also, if you have a more efficient way of doing the file name formatting, I would appreciate the info as well. It doesn't seem that Format is valid in VBScript, it is not listed in the table I found on msdn that lists the functions available in VBScript, although FormatCurrency, FormatDateTime, FormatNumber and FormatPercent are listed. Interestingly though, I do not get any syntax errors when I use Format, it just produces a null string.
'**********************************************************************
' Visual Basic ActiveX Script
' Rename to XXX15000_yyyymmdd_hhmmss format from inventory.txt file
'************************************************************************
Function Main()
On Error Resume Next
Dim strDestPath, strSourcePath, strFileType, strFileToMove
Dim fso
Dim strCurrentDate, strCurrentMonth, strCurrentDay, strLength, strMoveMonth, strMoveDay
Dim strCurrentHour, strMoveHour, strCurrentMinute, strMoveMinute, strCurrentSecond, strMoveSecond
Dim strDate, strTime, strMoveFrom, strMoveTo
' Initialize the variables
strSourcePath="d:\ABC"
strDestPath= "d:\LPP"
strFileType = "\XXX15000_"
strFileToMove="\inventory.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
'format the date and time so that month and day are always 2 positions
strCurrentDate = Now
strCurrentMonth = DatePart("m", strCurrentDate)
strLenght = Len(strCurrentMonth)
If strLength < 2 Then
strMoveMonth = ("0" & strCurrentMonth)
Else
strMoveMonth = strCurrentMonth
End If
strCurrentDay = DatePart("d", strCurrentDate)
strLength = Len(strCurrentDay)
If strLength < 2 Then
strMoveDay = ("0" & strCurrentDay)
Else
strMoveDay = strCurrentDay
End If
strCurrentHour = DatePart("h", strCurrentDate)
strLength = Len(strCurrentHour)
If strLength < 2 Then
strMoveHour = ("0" & strCurrentHour)
Else
strMoveHour = strCurrentHour
End If
strCurrentMinute = DatePart("n", strCurrentDate)
strLength = Len(strCurrentMinute)
If strLength < 2 Then
strMoveMinute = ("0" & strCurrentMinute)
Else
strMoveMinute = strCurrentMinute
End If
strCurrentSecond = DatePart("s", strCurrentDate)
strLength = Len(strCurrentSecond)
If strLength < 2 Then
strMoveSecond = ("0" & strCurrentSecond)
Else
strMoveSecond = strCurrentSecond
End If
strDate=(DatePart("yyyy", strCurrentDate) &strMoveMonth & strMoveDay)
strTime=(strMoveHour & strMoveMinute & strMoveSecond)
strMoveFrom=(strSourcePath & strFileToMove)
strMoveTo=(strDestPath & strFileType & strDate & "_" & strTime)
' Move the text file
If fso.FileExists(strMoveFrom) Then
fso.MoveFile strMoveFrom, strMoveTo
End If
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
April 5, 2002 at 1:35 pm
Havent had a chance to look at your code, but can offer help on Format. Compile a VB DLL that has one object with a Format function/method exposed, inside that just use Format as you normally would. You're just delegating the work. Came up with this as a way for Access 97 users where I work to use the Replace/Split functions that came with VBA6.
Andy
April 8, 2002 at 8:08 am
Connie,
I ran your script and all seems to work fine in WK2. There is however a typo in the code :-
strLenght = Len(strCurrentMonth)
It does not seem to cause a problem but may do when it comes to NT4.
Also, have you put MSGBOX's inside the FileExists Test?
Other than that it may be worthwhils re-checking permissions on the files and directories, based on the user authorising the execution of the dts.
Good Luck.
Simple Simon.
April 8, 2002 at 10:25 am
I just created a .vbs file to execute my script outside of dts. It works ok in W2K, but when I moved it to my production server (Win NT) the file extension was not identified. So I checked in c:\winnt\system32 and there is no wscript.exe, so it seems that wsh may be the problem. I will schedule wsh to be installed on production and see if it makes a difference.
April 8, 2002 at 11:09 am
Let us know what happens?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 10:11 pm
Success!! Once release 5.6 of WSH was installed on my NT server my script ran successfully both in batch and via dts.
Thanks everyone.
Also, thanks to swjs for spotting the typo which would probably have shown up as a bug in October.
April 12, 2002 at 3:55 am
I had a similar problem. That is :
* i wanted to do the same : move a file
* it worked in 2K and not in NT4
* i had another error message though : "Permission denied"
=> My solution was to close the connection (in the DTS Designer : properties of the transform data task , or was it the connection ? I dont remember; more probably the connection though) to the text file prior to executing the activeX script (with filesysobj) that wanted to move the file. Then the file was closed properly and it also worked in NT4
quote:
I have an active script task in dts that works in W2K, but does not work in Win nt 4.0.The purpose of the vbscript is to rename the output from a previous transformation step into a file name format that contains a date and time - basically my file name will be changed from something like name.txt to XXX111_20020404_101314
I'm using the exact same file names and directory structures on both the nt and the w2k machines, and I have verified the permissions on the files and directories.
I'm using the FileSystemObject and basically my code is:
If fso.FileExists(strMoveFrom) Then
fso.MoveFile strMoveFrom, strMoveTo
I have displayed the values in both the strMoveFrom and strMoveTo variables, and know they are correct, and the code works correctly on two other machines (both w2k). I also tried using MsbBox Err.Description after the MoveFile statement and the error displayed was 'object required'.
In my research I have found that the Windows Scripting Host is not delivered with Win NT 4.0 as it is with W2K, is it delivered with SQL Server 7.0? The vbscript parses correctly in nt and runs to completion, it just never moves the file.
Any suggestions?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply