DTS problem when scheduling as job

  • 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

  • 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

  • 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.

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

  • 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

  • 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

  • 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.  

  • 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

  • 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

  • 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

  • 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

  • I am using SQ Server 2000 by the way.

  • 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,

  • 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

  • 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