DTS problem when scheduling as job

Question

DTS problem when scheduling as job

pgraves

Ten Centuries

Points: 1105
More actions
January 16, 2007 at 5:18 am

#368851

Hi all,
I hope someone can help me with this as I am completely stumped.
I have a DTS package that uses an activeX script to loop through a bunch of files in a folder. The data in the files is then inserted into a table.
When running the DTS package from Enterprise Manager is runs perfectly fine. When I shedule the package to run as a job, the job begins executing but then just sits there. It never completes and never fails.
I am pretty sure it is not permissions related as this would give me an error. I even removed the input files so there was no processing to do and again the job just started executing indefinitely.
Any suggestions would be apreciated.
Thanks,
Paul

Viewing 15 posts - 1 through 15 (of 16 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

u directly can't omit the possibility of permissions problem. please check the permissions given for sql startup account. also if are very sure that its not a permission problem then write the output of the package execution to a text file so that u will know for sure whats happening in the execution.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

pgraves Ten Centuries Points: 1105 More actions · Answer 2

Sugesh,

The service is using the sqlservice account. I will try some different accounts, however, all the dts input files are local to the server. In fact, they are using the same directory when my scheduled backups are created so I know access is not a problem.

I can't output the package execution to a text file as the package never completes or fails. It simply says executing and stays like that until I stop the job.

I have never seen a DTS job that runs indefinitely but does not seem to do anything.

happycat59 One Orange Chip Points: 29384 More actions · Answer 3

Do you use any msgbox or inputbox commands in your activeX task ?

jfb SSCrazy Points: 2469 More actions · Answer 4

Hi

Maybe this is your problem...

http://support.microsoft.com/?kbid=290077

set all ActiveX scripting tasks that invoke the Windows Scripting Host shell object to execute on the main package thread.

I hope this help.

JFB

Wilbur Sean Bvuma SSC-Addicted Points: 425 More actions · Answer 5

one of the problems that i cam acros when running a DTS inside a job and does not get you the correct results, was because of the SQL Server Agent did not have access onto the directories of which the DTS was executing its processes at,

So I would suggest that you make sure the agent has full access into the direectories which you need the DTS to run at, cause your windows login might have it, but Windows SQL Server Login might not have the permissions

try that out

Simon Hundleby SSCarpal Tunnel Points: 4514 More actions · Answer 6

I had a similar problem where SQL server agent couldn't resolve the user account on the local machine where the source files were stored, in this case the user who owned the source files had no permissions on Sql server.

Will1922 SSCertifiable Points: 7189 More actions · Answer 7

My first guess off the top of head is that you have SQL Agent login account different than the SQL Server login account. I would check permissions between the two.

I have ran into similar problems, then made them both use the same account and everything worked fine.

Live to Throw
Throw to Live
Will Summers

pgraves Ten Centuries Points: 1105 More actions · Answer 8

All,

Thanks for your replies and suggestions.

I have had no luck and so have decided to run the DTS job in a batch file using dtsrun. I then schedule the job using Windows Scheduler and it runs perfectly fine.

The only problem I have now is that when the package completes, it requires me to click a 'Package has Completed' button. I can hide the package output by redirecting to a file but still have to click the complete button every time.

Does anyone know how to get the package to complete automatically without any user interaction.

Thanks,

Paul

Wilbur Sean Bvuma SSC-Addicted Points: 425 More actions · Answer 9

I have not yet came across a DTS that requires you to Click ok When Done,

Ouch, Which SQL Server Version are you using 2000 or 2005, and if you, can you most probably send a screen shot of how the "'Package has Completed' button" looks like

pgraves Ten Centuries Points: 1105 More actions · Answer 10

Wilbur,

I can't see how to attached or show a screen shot on this forum.

There is not much to show anyway.

I basically run the dtsrun from the command line. When it finishes up pops an OK button that says "Package has Completed". I click the OK button and it disappears along with the command window.

Paul

pgraves Ten Centuries Points: 1105 More actions · Answer 11

pgraves

Ten Centuries

Points: 1105

January 19, 2007 at 4:52 am

#684243

I am using SQ Server 2000 by the way.

Wilbur Sean Bvuma SSC-Addicted Points: 425 More actions · Answer 12

This is how i Execute my DTS,

--EXEC Master..xp_cmdshell 'dtsrun /s IP /u Username /p Password /n DTS_Name',NO_OUPUT

EXEC Master..xp_cmdshell 'dtsrun /s 192.168.0.1 /u Wilbur /p Bvuma /n Import_Client_Data',NO_OUPUT

You can either put this on a Job or Run in from your Query Anaylser

I have Jobs that are scheduled every morning to import data, validate and yah

So these scripts are currently in a stored procedure of which is executed by a DTS,

and the whole this is scheduled in the Job, all I have to is to check my emails id something does

go wrong in the imports

and you can also put the string e.g "dtsrun /s IP /u Username /p Password /n DTS_Name" in CMD

you can see how the DTS will run even with the results of whats happening

and yah try this out,

Will1922 SSCertifiable Points: 7189 More actions · Answer 13

Try to setup a schedule for your job like this :

1. go into DTS

2. right click on job to schedule

3. click on Schedule Package

4. Don't make any changes to the schedule and just click OK

5. Go into SQL Server Agent and then into Jobs

6. pull up properties for the job you just created

7. There should only be one step, edit that step and copy the full command in the step

8. copy this text into your nightly job as a type : Operating System Command(CmdExc)

Be sure to remove the Job that you just created in step 4!

If this still gives a prompt, then the prompt is coming from inside your DTS job, not from the schedule. This is the way that I had scheduled my jobs until I found out that youu could specify the name of the DTS job. It worked fine for me, but I had to generate a new command line each time I saved the job.

Just something different for you to try.

Live to Throw
Throw to Live
Will Summers

pgraves Ten Centuries Points: 1105 More actions · Answer 14

OK, problem solved.

I looked into some of the DTS steps. The developer was displaying a message box which was embedded deep down in a VB function. I couldn't find it at first.

Thanks for everyone's suggestions, it's appreciated.

Paul