DTS works but schedule JOB don't

Question

Post reply

DTS works but schedule JOB don't

NelsonVere

Ten Centuries

Points: 1280
More actions
March 20, 2002 at 10:01 am

#104834

I create a DTS to copy data and works fine when I run directly DTS, when I schedule a job on a daily basis, the job fail all the time..
error is ==> The Job was invoked by user sa,
the last step to run was step1.
and there is only one step there.
somebody knows what is that?
thanks

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

1) Is the place where tha package is built the same as the server it is saved to?

2) If not what version is the client and the server?

3) Are there differences that are on the client that do not exist on the server related to this package? (Mapped drive, etc.)

4) Have you tried creating an error handling file to output the errors when run for more detail? (Open package and select Package/Properties on the tool bar, found under general tab in SQL 7, and logging tab in SQL 2000)

5) Was the package scheduled from a 2000 server client on a 7 box?

6) Can you provide more details about the error?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

NelsonVere Ten Centuries Points: 1280 More actions · Answer 2

It's a SQL 2000 and the ODBC reside on the server and If I run manually, works fine

but the job dont work at all.

Dave Francis Old Hand Points: 328 More actions · Answer 3

If it is a scheduling problem, it could be a permissions issue with your SQLAgent account, as it is this that executes your jobs. Your SQLAgent account should be in your local admins NT group

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Unless agent is running under system account which if it is the ODBC DSN then make sure the DSN is a system DSN and not USER or File.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

NelsonVere Ten Centuries Points: 1280 More actions · Answer 5

The Agent is running under my name/password and i have administrator rights on that server

Antares686 SSC Guru Points: 125444 More actions · Answer 6

When the job was create it should have created a DTSRUN encrypted version that it executes, open the job step and copy it out, then run command line prompt to see what happens? If it fails create one yourself and see if works? Just trying to get a grasp on where the failure actually is occurring.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

maraclec Ten Centuries Points: 1175 More actions · Answer 7

Did you create the package while sitting at your workstation or at the server itself?

vascris SSC Journeyman Points: 75 More actions · Answer 8

Make sure when you create the package when you select the source you select it from the "Network Neighbouhood" not from a drive you have mapped on your PC, it will not recognize it.

vascris SSC Journeyman Points: 75 More actions · Answer 9

Actually when you select the "destination" not source, sorry.

ignacio Old Hand Points: 310 More actions · Answer 10

Microsoft has posted this article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

I think it brings some light to this issue.

kbrady SSCommitted Points: 1861 More actions · Answer 11

Here's an idea try recreating the project, and make sure that Owner is one who has permissions to run the package, preferable the SERVER\Administrator. Otherwise I like the IDEA of:

DTSRun /~Z0x62729286A921383A451F7E72B1ADE2D008AE95B7CB6C74A87E94540E1F62E8C0DB2BB79B287FB2FE1A51835096208D7AB70358CFBB8F681F46D46CB29020FC0B9382B93E51714C908FD7290139ECF01677D1C9C35FEFFFDC0E51ED8D6E00F0AE7B0638CD294AE2229523B201969F2742B05064

Grabbed from Steps--->Edit

Grab that and run it in query analyzer like so

EXEC master..xp_cmdshell 'DTSRun /~Z0xC4DCE73022D9D1818E5D424430B2C02CA25C602185BE4EF64609DE119B7F9A54D2733225B89C37DB3F708DE5A3EFDEB0B928E1B5F971039DD2BED27F09D0F399CC4F0EA19450EF0798D1C838DD4D1FE8868A7D9A2D13EFC1B5D110 '

GO

NelsonVere Ten Centuries Points: 1280 More actions · Answer 12

Sorry took me so long to answer I take vacations, so I'm back.

Yes when I copy and past and run from the query analyzer works fine, but the job still fails.

rocjohns SSC Rookie Points: 46 More actions · Answer 13

YOu need to install service pack 2. I had the same problem and once I installed service pack 2 everything was fine.

PAUL717 Old Hand Points: 398 More actions · Answer 14

I also had the problem. You need to register your entreprise manager with another login (not SA better a login that is used only to run the packages).

When this is done, edit your package and save as NEWNAME and then shedule the new one.