Executing DTS package

Question

Executing DTS package

BEIJCKM

Mr or Mrs. 500

Points: 583
More actions
April 16, 2003 at 12:22 am

#159653

Hi,
I'm executing a simple DTS package containing an ActiveX script.
Option Explicit
Function Main()
dim oFile
set oFile = CreateObject("Scripting.Filesystemobject")
If oFile.FileExists(DTSglobalvariables("PullFile")) Then
msgbox "file " & DTSglobalvariables("PullFile").Value & " exists."
Main = DTSTaskExecResult_Success
else
msgbox "file " & DTSglobalvariables("PullFile").Value & " does not exists."
Main = DTSTaskExecResult_Failure
End if
End Function
When I schedule this package it never complete's (20 hours of execution time). It seems that it's hanging.
Can anybody please tell me what's wrong .
Thanks in advance...
Regards,
Bart

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

dgermundson Hall of Fame Points: 3175 More actions · Answer 1

Couple of things to try:

1) Move DTSglobalvariables("PullFile")) out of the function and assign the value to a string before the call to FileExists.

2) Put a msgbox before and after the call to fileExists to see if it's hanging before the call to that function.

Darren

jfb SSCrazy Points: 2469 More actions · Answer 2

HI,

Are you using mapped drive?

You should use UNC path (\\servername\folder\filename.txt)

JFB

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 3

Hi JFB,

Thanks for the reply...

I'm currently not using a UNC path because the file I'm looking for resides on the same machine as the SQL server (...so it is not needed I think ?)

I've put a MsgBox before the existsfile call and this also is not working.

any other ideas...

Regards,

Bart

quote:
HI,
Are you using mapped drive?
You should use UNC path (\\servername\folder\filename.txt)
JFB

dgermundson Hall of Fame Points: 3175 More actions · Answer 4

This script is in the transormation section correct? If you have a msg box at the very beginning and it doesn't show up, then it must not even be getting this far. Have you tried this DTS package without any transformations?

Darren

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 5

Darren,

Thanks for the quick response.

Yes, I've just scheduled the package(ActiveX script) without the MsgBox. And this worked this worked, I mean I got an errormessage=1100... could this mean that I have security problems ? The file resides on the machine the SQL server is running on.

Regards,

Bart

jfb SSCrazy Points: 2469 More actions · Answer 6

Make sure the account is using SQL agent have permission on the network.

JFB

stuartb SSC Veteran Points: 268 More actions · Answer 7

Maybe these tips will help.

Be careful using msgbox in DTS packages.

These are great debugging but if you leave one in after you schedule the DTS package you'll get a hung package as the MSGBOX pops up (in the ether somewhere) and then waits for someone to press the OK (or whatever) button.

Use full UNC pathing for files, fileexists will need to know where the file is, as well as its name. When you exec the package in interactive mode (ie in DTS Designer) the file path will be defined by your context (ie in interactive mode DTS uses the DLLs on your client - not the server) so C:\temp become your clients C:\temp NOT the server's C:\temp. For this reason using UNC pathing seems like a good standard.

Also when you exec packages in DTS Designer - interactively - your security context is used NOT the SQLAgent, but when you schedule the package it now uses the SQLAgent to run and hence takes on SQLAgent's security context.

Knowing and understanding the context within which you are working (run time to interactive) can save you a lot of heartache.

Also I've seen problems with the versions of vbScript.dll and scrrun.dll on machines, make sure you have the correct versions (sorry can't remember what these are something like 5.6....).

Check the error number against technet to determine what the real problem is - did it give you any other hints like "Cannot create object?"

Great site for DTS info: www.sqldts.com

Cheers,

Stu

avi_a Mr or Mrs. 500 Points: 527 More actions · Answer 8

It seems to me the msgbox is hanging your script.

When SQLAgent tries to execute a msgbox(), the msgbox appears on the SERVER SCREEN (if you have a pcanywhere/vnc to your SQL box you can see it popping up and waiting for someone to click "OK").

What's more, the only way a service (in this case - SQLServerAgent service) can be allowed to display anything to screen (i.e. - a msgbox) is if it's configured to run under "Interactive User" identity. If the SQLServerAgent is assigned a different identity (as is recommended), you'r msgbox will not appear anywhere(!), but will still wait for someone to click "OK" on it.

Sad but true..

- Avi

BEIJCKM Mr or Mrs. 500 Points: 583 More actions · Answer 9

Stuart_b and avi_a,

Thanks for your replies.

The information you gave me is very helpfull, now I understand what the problem is... It's just a matter of security...

Greetings,

Bart