Package fails when executed by the Job

Question

Package fails when executed by the Job

Hemant Kumar

Ten Centuries

Points: 1063
More actions
December 12, 2005 at 5:20 am

#67114

Hai ....,
I have a DTS Package that is used to dispatch mails to all the customers. When I execute the package manually by selecting it and then clicking Execute Package from the context menu, it executes to completion successfully. But when I schedule to execute it using Job, then I get the following error message:
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147467262 (80004002); Provider Error: 0 (0) Error string: No such interface supported Error source: Microsoft OLE DB Provider for SQL Serv... Process Exit Code 1. The step failed.
FYI: This Package is under machine7. When I copy the package to my system (machine3) and execute it using Job, it succeeds
Please help me out of this problem

Viewing 9 posts - 1 through 8 (of 8 total)

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

When you run the package on machine3, even though the source of the package is on machine7, the package runs on machine3.

Try loging on to machine7 with the account used to run the SQL Server Agent and then run the package.

There is probably some software or configuration that exists on machine3 that does not exist on machine7. For example, you might have an ODBC DNS defined on machine3 that is not defined on machine7.

SQL = Scarcely Qualifies as a Language

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 2

No Carl,

There is no DSN or anything that is in my machine that is different from machine7 that I have used in the Package. I have created this package from my machine (machine3) and it shows this under the owner column when displaying the list of DTSs. I guess that if I have created the DTS from machine7 itself, then this problem MAY vanish. But ofcourse, this is not the solution.

Please let me know if you or anyone have other ideas to overcome this problem

Regards,

Hemant

john-245948 SSC Rookie Points: 47 More actions · Answer 3

Just a guess but try this: on the General tab when you create a job select 'sa' as the owner.

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 4

Yes John,

The owner is sa, but still the problem remains

John Laska SSC-Addicted Points: 454 More actions · Answer 5

This may or may not pertain, depending on how you are doing the mailing. Assuming you are using xp_sendmail:

When you run the job manually, it will use your mail profile to send the emails, when it is run through a job, it will try to use the mail profile for the username which the SQL Agent Service runs as, unless you specifically send execute xp_startmail first. So, if you have a user MyDomain\SQLSvc which is used to run the SQL Agent service, that user must have a mail profile on the server.

As such, you have 2 options:

1. Login as that user on the server, install and configure Outlook. This will create a mail profile for that user.

2. In the step of your package where you are doing the xp_sendmail start off with an xp_stopmail then do and xp_startmail and pass the procedure parameters for your profile.

As an example, from a script I use to crawl through a database and send mail:

exec xp_stopmail

exec xp_startmail

@user='SQLSvc',

@password='password'

exec xp_sendmail

@recipients = 'SomeUser@SomeDomain.tld;AnotherUser@SomeDomain.tld',

@message = 'Your message here',

@query = 'exec usp_CheckDates',

@subject = 'My Subject',

@no_header = 'TRUE',

@width = 150,

@dbuse = 'DB'

exec xp_stopmail

GO

This stops the mail service on SQL (if it is running); starts the mail service with the supplied credentials; sends the mails as created; and finally, stops the mails service.

John Souza Grasshopper Points: 13 More actions · Answer 6

I've dealt with similiar problems like this a million times... This the one thing I hate about DTS; it's easy as hell to create a cool package but difficult as hell to implement...

Anyways, there could be several different reasons for the root of this problem. One could be the ownership (which, you say is sa).... Is sa set up to send sql mail? who are you running the SQL Agent from? Basicly, to resolve any security conflicts, try this:

Log in to the server as the account you want to run the job from.
Open the package on the server (machine you plan to run the job).
Save the package as a different name on the server.
Try to run this package. If it fails, you prob need to set up SQL mail for this account on this computer.
If it does not fail, then schedule the job.
After all of this, change the account name that runs the SQL Server Agent over to this account.

My experience has been that creating a domain-based service account is useful for situations like this (that has an exchange profile linked to it).

If all else fails (or you get sick of messing with exchange), do what I ended up doing: download a DTS tool that will do simple SMTP emailing instead of using SQL Mail.

Good luck.

--Johnny

whdsql SSC Veteran Points: 270 More actions · Answer 7

It wasn't clear if you were using a query when sending your email, if you are, it's possible that it's returning an error or warning, that could be why it works on one server and not the other... if the data was different. I get this same problem whenever I get the warning " NULL values eliminated by aggregate function..."

Hemant Kumar Ten Centuries Points: 1063 More actions · Answer 8

First...Thanks everyone trying to help me solve the problem

I 'm using sp_OACreate method to send mails to the customers and it is working perfectly alright and have tested a sample with my mail id and i do receive the mail.

Then I decided to execute the package directly at the server (machine7) and saw that the step 3 of my package fails with the following error message window being displayed :

Error Source: Microsoft OLEDB Provider for SQL Server

Error Desc: The task reported failure on execution. No such interface supported

I 'm not at all getting this error message when i execute the same package manually . Hope this error message will help you understand the problem with my server (machine7) but i 'm not able to identify what should be done

Expecting a solution to the problem.

Thanx and Regards,

Hemant