January 16, 2007 at 5:18 am
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
January 16, 2007 at 5:24 am
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
January 16, 2007 at 7:21 am
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.
January 16, 2007 at 11:39 pm
Do you use any msgbox or inputbox commands in your activeX task ?
January 17, 2007 at 7:50 am
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
January 17, 2007 at 8:06 am
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
January 17, 2007 at 9:07 am
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.
January 17, 2007 at 9:17 am
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.
January 19, 2007 at 4:10 am
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
January 19, 2007 at 4:18 am
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
January 19, 2007 at 4:49 am
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
January 19, 2007 at 4:52 am
I am using SQ Server 2000 by the way.
January 19, 2007 at 5:58 am
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,
January 19, 2007 at 6:40 am
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.
January 19, 2007 at 7:55 am
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply