Permission Denied w/ SQL Server 2005

[mcauliff]

I have a DTS Package with multiple tasks. It is executing to completion under SQL Server 2000. In executing the DTS Package under SQL Server 2005, it receives a "Permission Denied" error in one of the tasks. The package is executed on the server with sysadmin authority.

I don't understand what permission it is looking for.

Error Message:

Error Source: Microsoft Data Transformation Services (DTS) Package


Error Description: Error Code: 0
Error Source=Microsoft VBScript runtime error
Error Description: Permission denied

Error on Line 29

Line 29 is filesys.CopyFile getFolderName(DTSGlobalVariables("gvImportFilePath").value) & "\" & objFile.name, ImportFileName


Source:





'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'** Create global variable to handle duplicate time stamp

Dim intFileCount
intFileCount = 0


Function Main()
dim filesys
Dim ImportFileName
'***** For Debugging purpose
'***** strWriteToFile = "\\dtw-chgptsql1\import\ChrisAPI.txt"
'*** End Debugging set up

set filesys=CreateObject("Scripting.FileSystemObject")
Set demofolder = filesys.GetFolder(DTSGlobalVariables("gvImportFilePath").value)
Set filecoll = demofolder.Files

'**** For Debugging purpose
'**** strData = "Value of filecoll " & filecoll & VBCrlf & _
'**** "Value of ImportFileName is " & DTSGlobalVariables("gvImportFilePath").Value & VBCrlf & _
'**** "Value of DestinationPath is " & DTSGlobalVariables("gvDestinationPath").Value
'**** Call WriteToFile(strWriteToFile, strData, 10)
'*****

for each objFile in fileColl
ImportFileName = getFolderName(DTSGlobalVariables("gvImportFilePath").value) & "CHQ_HRISInterface " & GetDateTimeString() & ".csv"
filesys.CopyFile getFolderName(DTSGlobalVariables("gvImportFilePath").value) & "\" & objFile.name, ImportFileName
filesys.DeleteFile getFolderName(DTSGlobalVariables("gvImportFilePath").value) & "\" & objFile.name
filesys.MoveFile ImportFileName, getFolderName(DTSGlobalVariables("gvDestinationPath") )
next
Main = DTSTaskExecResult_Success
End Function

Function GetDateTimeString()
Dim strFullDate
Dim CurrentDate
CurrentDate = now()
'*** increment the value of intFileCount
intFileCount = intFileCount + 1

strFullDate = Cstr(year(CurrentDate)) & "-" & retTwoNumber(month(CurrentDate)) & "-" & retTwoNumber(day(CurrentDate))

strFullDate = strFullDate & " " & retTwoNumber(hour(CurrentDate)) & retTwoNumber(Minute(CurrentDate)) & retTwoNumber(Second(CurrentDate))
strFullDate = strFullDate & "." & intFileCount

GetDateTimeString = strFullDate
End Function

Function getFolderName(name)
if right(name, 1) <> "\" then
getFolderName = name & "\"
else
getFolderName = name
end if
End Function

Function retTwoNumber(number)
if number < 10 then
retTwoNumber = "0" & Cstr(number)
else
retTwoNumber = Cstr(number)
end If
End Function

' Used for Debugging purpose
Sub WriteToFile(strFilePath, strData, iLineNumber)
Dim objFSO, objFile, arrLines
Dim strAllFile, x
Set objFSO = CreateObject("Scripting.FileSystemObject")
strAllFile = ""
If objFSO.FileExists(strFilePath) Then
Set objFile = objFSO.OpenTextFile(strFilePath)
If Not(objFile.AtEndOfStream) Then
strAllFile = objFile.ReadAll
End If
objFile.Close
End If
arrLines = Split(strAllFile, VBCrlf)
Set objFile = objFSO.CreateTextFile(strFilePath)
For x = 0 to UBound(arrLines)
If (iLineNumber-1) = x Then
objFile.WriteLine(strData)
objFile.WriteLine(arrLines(x))
End If
Next
If iLineNumber >= UBound(arrLines) Then
objFile.WriteLine(strData)
End If
Set objFile = Nothing
Set objFSO = Nothing
End Sub

 

[drtree]

Hi,

Are you running the SQL Server Agent accounts under different accounts in 2000/2005? It looks like one of them may have permissions to copy the file (via the code in line 29) and other account does not...

HTH,

Doc Tree

 

[mcauliff]

drtree

Thank you for the quick reply.

The error occurred when executing manually. Does the SQL Server Agent come into play during manual execution? I thought it was only when scheduled.