Loading ...
Sorry, an error occurred while loading the content.

DTS inquiry

Expand Messages
  • Leida Dougherty
    I am having a dire problem running a DTS process on SQL Server as a scheduled job, can anyone help me? Situation: Whenever I run the DTS package manually as
    Message 1 of 2 , Apr 3, 2006
    • 0 Attachment

      I am having a dire problem running a DTS process on SQL Server as a scheduled job, can anyone help me?

      Situation: Whenever I run the DTS package manually as myself (being a Windows Authentication account), on the server, it runs successfully. When I schedule it as a job it does not run successfully? I get the following error:

      Step Error Source: Microsoft Data Transformation Services (DTS) Package

      Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code 2, which does not match the specified SuccessReturnCode of 0.

      Step Error code: 80040496

      Step Error Help File:sqldts80.hlp

      Step Error Help Context ID:4900

      This DTS process needs to use mail to notify (internal) users, so I have to be careful of the service account I am using. I am currently using a domain user account called smssql that runs successfully when I do it manually, but try and schedule it and it keeps on aborting.

      I have not worked with DTS long enough to know what the best method is. Do I have to resort to running this outside of SQL SERVER?   

      Help! I would greatly appreciate your thoughts and/ or suggestions on this matter.

      Leida Dougherty
      CAE USA Inc. 
      IT Department
      4908 Tampa West Blvd.
      Tampa, Florida 33634
      Email: Leida .Dougherty@...
      Phone: 813-887-1485  

       


      Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person) you may not use, copy, disclose or deliver this message to anyone. If you have received this message in error, kindly notify the sender by reply email or at the phone number listed above, and then delete this message.
    • Jeff Duncan
      First 1. I know you said you are running as smssql but I want to make sure that SQLSERVER and SQLAGENT are running as this ID. 2. I need you to make sure
      Message 2 of 2 , Apr 3, 2006
      • 0 Attachment

        First

        1.   I know you said you are running as smssql but I want to make sure that SQLSERVER and SQLAGENT are running as this ID.
        2.   I need you to make sure you have a mail profile set up on the server for smssql and it is set up as the mail client in SQL.  And that you are using xp_sendmail
        3.   The scheduled task needs to run as an owner like sa.  Not your windows account.  If your windows account is the job owner and you are not logged in it will fail also.
        4.   If you would like help you can call me at my desk directly at 813-414-7008.

         

        Jeff Duncan 

         

        Office  813-414-7008

        Cell    813-323-2615


        From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Leida Dougherty
        Sent: Monday, April 03, 2006 1:47 PM
        To: tampasql@yahoogroups.com
        Subject: [tampasql] DTS inquiry

         

        I am having a dire problem running a DTS process on SQL Server as a scheduled job, can anyone help me?

        Situation: Whenever I run the DTS package manually as myself (being a Windows Authentication account), on the server, it runs successfully. When I schedule it as a job it does not run successfully? I get the following error:

        Step Error Source: Microsoft Data Transformation Services (DTS) Package

        Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code 2, which does not match the specified SuccessReturnCode of 0.

        Step Error code: 80040496

        Step Error Help File:sqldts80.hlp

        Step Error Help Context ID:4900

        This DTS process needs to use mail to notify (internal) users, so I have to be careful of the service account I am using. I am currently using a domain user account called smssql that runs successfully when I do it manually, but try and schedule it and it keeps on aborting.

        I have not worked with DTS long enough to know what the best method is. Do I have to resort to running this outside of SQL SERVER?   

        Help! I would greatly appreciate your thoughts and/ or suggestions on this matter.

        Leida Dougherty
        CAE USA Inc. 
        IT Department
        4908 Tampa West Blvd.
        Tampa , Florida 33634
        Email: Leida .Dougherty@...
        Phone: 813-887-1485  

         


        Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person) you may not use, copy, disclose or deliver this message to anyone. If you have received this message in error, kindly notify the sender by reply email or at the phone number listed above, and then delete this message.

         

        -------------------------------------------------- 
        -----   This email bound by the following:   ----- 
        ----  https://www.gtefcu.org/disclaimer.asp    ---- 
        -------------------------------------------------- 
        
      Your message has been successfully submitted and would be delivered to recipients shortly.