DTS ActiveX :: Delete Files and DB Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS ActiveX :: Delete Files and DB Records

Author

Topic

kingroon
Starting Member

29 Posts

Posted - 2006-02-13 : 18:51:01

Hi there,

Objective ::
Run a DTS job once a week that cleans out all "Turing" string records from DB Table and deletes image files from contiguous directory.

Problem ::
Despite using an ASP page to retrieve the Live File Path of the images, the VB in the AX doesn't like it:

Error Source= Microsoft VBScript runtime error
Error Description: Path not found
Error on Line 36

Line 36 reads as:

Set folder = fs.GetFolder(path)

Where 'path' is declared earlier and set explicitly [not using Server.MapPath('web/dir/') since this will not work]. The path is set in the following format [excuse the * use]:

path = "X:\Inetpub\vs******\images\turing\"

So? ::
Can anyone tell me why the path MIGHT be wrong, or why it might be INTERPRETED as incorrect? I have checked with my tech help and they have confirmed the directory DOES EXIST. Would this be a permissions issue? Might it be a user issue i.e. the user [dbo] trying to run the DTS might not have permissions to view the directory etc.? I'm stumped..

Thanks for any help..

Rock on,
KingRoon

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-13 : 19:04:11

Does x interpret to the drive letter ?

You paste your code here, the server.mapPath should work in your ASP script

kingroon
Starting Member

29 Posts

Posted - 2006-02-13 : 19:16:55

Yes, X:\ is the Drive Letter.

The MapPath does work in my ASP yes, but I'm writing in VB in my ActiveX Script.

Is the GetFolder() method valid using the FSO in VB?

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-02-13 : 19:32:00

Sorry, But am not really an active X person.

But i do know, that its best not to put the drive letter, it shoudl be from the root "/"

try that and see what happens
Afrika

kingroon
Starting Member

29 Posts

Posted - 2006-02-13 : 19:45:16

Righto, well to anyone else out there here is the VB code in my ActiveX Script...

Dim path, item
path = "X:\Inetpub\vs******\images\turing\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set folder = fs.GetFolder(path) ' Line 36!

For Each item In folder.Files
If fs.FileExists(item) And DateDiff("n", item.DateLastModified, Now()) > 30 Then
fs.DeleteFile(item)
End If
Next

Thanks in advance..

Rock on,
KingRoon

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-13 : 23:37:40

Where are you executing this DTS job ?
The DTS package runs on the workstation that you are working on, so if that path is valid for the webserver, but you are clicking the "Run Package" from your dev machine, that path won't be valid.

To make it run in the context of the server, schedule it as a job.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

kingroon
Starting Member

29 Posts

Posted - 2006-02-13 : 23:42:11

Hi Merkin,

It's running on the Live server [have to get the hosting guys to run it for me] as a job. I reckon the problem might be a permissions issue? I can run/schedule the DTS job [using a different/local file path] on the Dev machine no problemo..

Rock on,
KingRoon

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-13 : 23:45:18

Is drive X: a physical drive ? Or a mapped network drive ?

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

kingroon
Starting Member

29 Posts

Posted - 2006-02-13 : 23:51:18

It's a physical drive.. The DB sits on the same box as the website [eesh..] so I should be able to create the FSO no problem..

Thanks for your help on this Merkin, it's good to go through these things logically for sure..

Do you think it might be a permissions thing?

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-14 : 00:05:42

quote:
Originally posted by kingroon

Do you think it might be a permissions thing?

Nope

I just set up a job that calls GetFolder to get a handle on a folder that it has no permissions on. Job works fine.

I'm 99% sure you have the path wrong.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

kingroon
Starting Member

29 Posts

Posted - 2006-02-14 : 17:50:24

Hey Merkin,

Had a nice message from my Hosting Suuport this morning..

"The appropriate NTFS permissions have been granted to the directory and the job has now run successfully. Please let us know if there are any problems."

Thanks for your help on this..

Rock on,
KingRoon..

Chaotician Man,
Slice the lines of virgin pathways.
Harmony Hero.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-14 : 18:05:55

Wow, that's really bizarro. I couldn't replicate the problem by denying permissions.

One for the books....

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources