Run-time error 75
-
I have a small VB app that renames an Excell file from a DTS package. It works fine from within VB, and even when I convert the app into an .exe. But when I execute it from a DTS pakage(using the Execute Process Task) I get a "run-time error 75 path/file access error". What am I doing wrong?
Please help
-
Possibly security/permissions? Keep in mind that when you run things from within DTS, they will pick up the user context of the account that DTS is set to run under.
HTH,
Steve.
Steve.
-
I ran it under the account that SQL Server is running on, and still no luck.
Can you be a little more specific as to what I'm doing wrong?
Thanks
-
When a package is executed it assumes the security context of whoever/whatever triggered the execution.
eg: When you execute the package it runs under your security context, when SQL Agent executes the package it runs under the security context of the SQL Agent account.
Make sure the account that is executing the package has read/write access to the path & file.
Also, why do you have a VB app to rename the file? You can establish the same functionality using an ActiveX Script Task and writing a little bit of VBScript code to manipulate the file using the FileSystemObject.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I have never used an ActiveX Script Task, and I'm not familiar with VBScript functions.
Do you have a sample code that I can modify and use to rename my file to the filename and the appropriate date?
Thank you
-
You'll find that VBScript is very similar to VB6 , but without the strong data typing.
If you send thru your code, I can help modify it if you like and send backj.
Steve
Steve.
-
Here you go Steve, and thanks
Dim iToday As String
iToday = Format(Now, "mmddyyyy")
Name "\\strategic\APPS-SRV1\Output\daily_extract.xls" As "\\strategic\APPS-SRV1\Output\daily" & Left(iToday, 2) & "_" & Mid(iToday, 3, 2) & ".xls"
Unload Me
-
OK, its a lot longer, and has no error handling, but should do the trick til you get some more time on VBSCript.
I would have liked to have used the Datepart function to retrieve the Month and Day, but on my machine, they ain't working :-S.
I have tried to comment it out so you can see whats happening. Create a new DTS package, put an ActiveXScript task onto it, and the open the task, delete it's current contents, and paste the code below in.
Keep in mind that this has no error handling, so checking for the ecistance of the file prior to trying to set the "f1" reference to it would be a very nice idea. 😀
Hope this helps,
Steve.
OPTION EXPLICIT
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Name = "\\strategic\APPS-SRV1\Output\daily_extract.xls" As "\\strategic\APPS-SRV1\Output\daily" & Left(iToday, 2) & "_" & Mid(iToday, 3, 2) & ".xls"
Dim fso, f1 'Used for FileSystemObject and File Object
Dim sFileName' Used for holding the new file name
Dim sNow' Used to hold the current date
Dim sPath' used to save me typing 🙂
' Set the Path to original and new file
sPath = "\\strategic\APPS-SRV1\Output\daily"
' Create the filesystem object
Set fso = CreateObject("Scripting.FileSystemObject")
' Set the reference to the current file
Set f1 = fso.GetFile(sPath & "_extract.xls")
' Get the current date
sNow = Now()
' Call a few helper functions to get a nicely formatted Month and Day and concat to the path
sFileName = sPath & ReturnMonth(sNow) & "_" & ReturnDay(sNow) & ".xls"
' Use this for testing 🙂
'Msgbox(sFileName)
' Rename the file
f1.Name = sFileName
Set f1 = Nothing
Set fso = Nothing
' Exit the task with success
Main = DTSTaskExecResult_Success
End Function
' *** Helper Function to format Month nicely ***
Function ReturnMonth(dtDate)
Dim iMonth
Dim sMonth
iMonth = Month(dtDate)
if iMonth < 10 Then
sMonth = "0" & iMonth
Else
sMonth = CStr(iMonth)
End If
ReturnMonth = sMonth
End Function
' *** Helper Function to format Day nicely ***
Function ReturnDay(dtDate)
Dim iDay
Dim sDay
iDay = Day(dtDate)
if iDay < 10 Then
sDay = "0" & iDay
Else
sDay =CStr(iDay)
End If
ReturnDay = sDay
End Function
Steve.
-
Argghhh, all my work doing tabs, and they got blatted when posting!?!?!
Steve.
Steve.
-
I know, the formatting for code isn't the best, but they're working on it
I like your use of the extra functions for the month and day. I usually do this as,
Right("00" & month(Date()),2) & Right("00" & Day(Date()), 2) & Year(Date())
Makes for long lines, which I hate
garutyunyan, you should also check out http://www.sqldts.com they provide a lot of good sample code.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 03/19/2003 03:47:53 AM
Edited by - phillcart on 03/19/2003 03:49:21 AM
Edited by - phillcart on 03/19/2003 03:50:41 AM
--------------------
Colt 45 - the original point and click interface -
Phil, I like the concat of the double zero, cool. Would get around the crappy VBscript non-typing that loves to switch vars to/from ints and strings 🙂 .
I could have sworn that I used to use a format function but couldn't find it when looking yesterday.
Steve.
Steve.
-
Steve, I just sent an email with the error I got from the package. Would you please reply?
-
Apologies Gary,
The error lies in the renaming line.
I tested this prior to adding the path and name string variable, it basically doesn't like the path in there as well when renaming, it expects only the new filename.
Check your email, I've sent you the code again, but with the mod to make it work.
Steve.
Steve.
-
Steve
VBScript does have a FormatDateTime function, however unlike VB it does not allow you to specify a formatting string. You can only specify one of five pre-defined constants, eg: vbShortDate, vbLongDate, etc...
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
Yeah, and they're all based on your machine locale settings ..... 🙁
Steve.
Steve.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply