Help with strange DTS problem

Question

Help with strange DTS problem

Kathi Kellenberger

SSChampion

Points: 11811
More actions
October 27, 2003 at 8:15 am

#106741

I have a DTS package that doesn't run correctly when it runs from a scheduled job. It looks like it is running a previous version of the package when it runs scheduled -- it doesn't execute some of the sql tasks.
I am using an unencrypted dtsrun command with the package name, not a guid. Since I discovered the problem today, I have given the package a new name with the "Save As" command and updated the job to reflect the new name. I am still experiencing the same behavior. When I run the package manually, it runs perfectly, but when scheduled, it doesn't work correctly. Both ways report success.
This is SQL 2000 SP3.
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

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

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I finally found a logic error with the package. What is odd is that it actually ran correctly when run manually.

There are 4 independent multi-step processes running. The last task in two of them were switched. So, depending on the order in which the 4 processes completed I would see the different results. The fact that I tested the package by running it manually obscured the fact that it wasn't correct.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

John_McDonald@tfcomp.com Newbie Points: 5 More actions · Answer 2

I have a DTS package that doesn't run correctly when it runs from a scheduled job. It looks like it is running a previous version of the package when it runs scheduled -- it doesn't execute some of the sql tasks.

I am using an unencrypted dtsrun command with the package name, not a guid. Since I discovered the problem today, I have given the package a new name with the "Save As" command and updated the job to reflect the new name. I am still experiencing the same behavior. When I run the package manually, it runs perfectly, but when scheduled, it doesn't work correctly. Both ways report success.

This is SQL 2000 SP3.

Thanks,

Kathi

[DTS packages are qurkie but are easy to create; so i do not keep versions. Keep the requirements documentation instead.

After i've make a simple change via "design package" i exit design editor, saving the change. Then i right click the package name, select versions and blow the old version away.]

hoosierpride Mr or Mrs. 500 Points: 565 More actions · Answer 3

This problem is commonly caused because you have referenced the package version in the job statement. Here is a simple way to create your own execution line and use it in your job.

1. From the command line Run DTSRunUI.exe.

2. Select the Package you wish to set up a command line for.

3. Click Advanced

4. Add any global variables you are setting.

5. Click Generate

6. Copy the line out of the text box and put it in your job step.

7. Cancel the process

You can also encrypt the command if desired.

Hope this helps.

Steve Hughes, MCDBA, MCSD

Magenic Technologies

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

Thanks for your replies. I actually found a logic error in the package that was causing my problem.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor