Post reply

Package fails when executed by the Job

  • 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

  • December 12, 2005 at 6:03 am

    #608973

    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

  • December 12, 2005 at 7:36 am

    #608997

    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

  • December 13, 2005 at 9:06 am

    #609243

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

  • December 13, 2005 at 9:25 am

    #609250

    Yes John,

                 The owner is sa, but still the problem remains

  • December 13, 2005 at 10:52 am

    #609270

    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.

  • December 13, 2005 at 10:57 am

    #609271

    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:

    1. Log in to the server as the account you want to run the job from.
    2. Open the package on the server (machine you plan to run the job). 
    3. Save the package as a different name on the server.
    4. Try to run this package.  If it fails, you prob need to set up SQL mail for this account on this computer.
    5. If it does not fail, then schedule the job.
    6. 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

  • December 13, 2005 at 12:48 pm

    #609298

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

  • December 13, 2005 at 10:13 pm

    #609394

    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

     

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

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