Weird issue with DTS packages scheduled as a job.

Question

Weird issue with DTS packages scheduled as a job.

Chad Smith

SSC Rookie

Points: 38
More actions
July 15, 2004 at 2:08 pm

#85917

Hi everyone,
I'm having a weird issue with one of our scheduled jobs. I created a DTS package that is exporting data, users and roles between 2 servers. It's set to drop the objects on the target and then copy over the same objects from the source. The problem is one of the database roles is getting it's permissions nixed somehow and I can't figure out why..... some of it's permissions come across and the it's like it just stops halfway through and remaining permissions don't get set within the role. In Enterprise manager, the job shows that it completed successfully, but I can go to the database, bring up the role and look at it's permissions and see that not all needed permissions came across as they exist on the source server.
Additionally, If I execute the package it works without any problems and the role comes across just fine. After scheduling the job to run at the same time each day in Enterprise manager, I can even go to management, jobs, and right-click on the scheduled job and choose start job, the job will run, complete successfully and the role yet again comes across just fine -- it's only happening when it actually runs at the scheduled time????
However, as I stated before, everytime this job executes at it's scheduled time, the job's log will says it's been successful. In the meantime while trying to figure out why this is happening, I've been having to go in each morning and manually updating the role on the target server so that folks can use the application working with this instance.
I thought maybe something else might have been occurring on the server at this time but there are no other jobs nor any connection activity occurring on this server at this time... I thought it might be a permissions problem with the owner of the DTS package but that's not it since the tables, data, and referential integrity all come over just fine .. it's just the database role that's having an issues.... Also, I have deleted and recreated the DTS package serveral times (thinking maybe something was missed during creation of the package) and the issue still exists. I can't think of anything else to check here.....
Has anyone else experienced the same or similiar issue with scheduled jobs ?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

Hi Smith,

Firstly I don't know whether my answer would help you or not because these kind of weird errors would come possibly for the configuration reason.

Next I would like you to cross check the job went successfully or not. I mean whatever the inserts,updates and deletes the job was suppose to do is done properly. If it is done properly then it is for sure your package design is not proper.

Now I will come to the point for Weird errors. Normally it comes when your DTS Step is not running on the main thread. If you have dynamic properties in your package Go to the steps Set the ExecuteInMainThread to 1. By default it will be 0.

Try this out - I was also facing a simmilar kind of problem when I run the package I get an weird error OleDb property ' ' is already in the collection. I fixed the same way.

By the next time when you post the message give the error description because that would really make the people to reply promptly.

Thanks,

Ganesh

jordanac Hall of Fame Points: 3653 More actions · Answer 2

I have had a similar experiance where the JOB is successfull and it did not complete some steps. Use your DTS package logs to see what steps took 0 ms to complete. This gives you an idea where the fault might be.

I tried everything and gave up. My solution was to create a totally new DTS package with a new JOB. And it worked. Why? Do not know.

Andy.

conrad coetzee SSC Enthusiast Points: 112 More actions · Answer 3

I’ve stopped using enterprise manager to schedule my DTS packages, just to many problems.

I use the dtsrun Utility and create a .Bat file and then execute the .bat file from Scheduled Tasks.

(See BOL dtsrun Utility)

MarkG Ten Centuries Points: 1237 More actions · Answer 4

I'm not sure this is your problem or not, but I had a similiar issue where the DTS runs manually just fine, but not as a Job. Here's what someone told me from the mssql maillist group: (check the link out - at the bottom of that page there's another link about Mapped Drives - this is what fixed my problem - not the security context - DTS will run using Mapped Drives (Drive x:\datafolder\) for a JOB to run a DTS pkg, the Connections in the DTS Pkg must use UNC's (\\sharename\datafolder\ *note the double wacks in front of the sharename ) - Mark

When you run the DTS package manually, it runs on your computer under your user context.

When you schedule a DTS package it runs through SQL Server Agent on the SQL Server in question. In that case, if the job owner is a member of the sysadmin role, it runs under the context of the SQL Server Agent service account. If the job owner is not a member of the sysadmin role, it runs under the context of the proxy account, if that has been set.

More here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;269074

HTH.

Brian Kelly

marymac SSC-Addicted Points: 436 More actions · Answer 5

You probably already thought of this, but try checking to see if other jobs are running at the same time your package is scheduled to run. Sometimes other jobs, like backups, etc can cause DTS packages to partially fail even though the job history says it ran just fine.