Post reply

Task is now failing - it worked yesterday

  • June 16, 2005 at 9:47 am

    #66566

    Ok, I have not changed anything.  I can run the DTS package manually by right clicking on the package in the Local Packages list.  When the package is scheduled to run, it fails.   It worked fine yesterday and for about a month before that on a daily basis.

    The table that it's trying to load is small and there is over 500mb free in the database. 

    Here's the output from the failure:

     

    DTSRun:  Loading...

    DTSRun:  Executing...

    DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1

    DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1

    DTSRun OnStart:  DTSStep_DTSDataPumpTask_1

    DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000

    DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000

    DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000

    DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000

    DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147221499 (80040005)

       Error string: 

       Error source: 

       Help file: 

       Help context:  0

     

    Error Detail Records:

    DTSRun OnFinish:  DTSStep_DTSDataPumpTask_1

    DTSRun:  Package execution complete.

    Live to Throw
    Throw to Live
    Will Summers

  • June 16, 2005 at 10:04 am

    #566179

    Is there a possibility that bad data is occuring in the 5th 1000 rows set??

    Maybe there's a difference in the datatypes of the import table and a truncatation or impossible cast is occuring.

  • June 16, 2005 at 10:06 am

    #566181

    If the data was bad on the source server, then why would I be able to run the DTS package manually?

    Live to Throw
    Throw to Live
    Will Summers

  • June 16, 2005 at 10:19 am

    #566200

    Sorry, missed that part.

    No other idea then. Maybe Jonathan Stokes can help you.

  • June 16, 2005 at 10:46 am

    #566211

    The main difference to running a dts manually versus scheduled is the user account running the package.

    I suspect that the user account for sql server agent account has lost previous priveleges that it had before.

    Hence, running it manually it uses the client login, which in this case is OK.  

    Check out the sql agent account and see if the password has changed or permissions to a table or stored prcoedure have changed etc.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 16, 2005 at 10:55 am

    #566212

    I knew you could help him . I must be able to see the future or something

  • June 16, 2005 at 10:59 am

    #566214

    The SQL agent logs into the local account, just like SQL server. 

    The local account is still the same as yesterday.

    I can run other DTS packages that access the same server through a scheduled job.

    Live to Throw
    Throw to Live
    Will Summers

  • June 16, 2005 at 11:12 am

    #566217

    Is it a text file you are importing.

    Have the permissions changed on the file server where the source text file or whatever other source it is resides?

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 16, 2005 at 11:15 am

    #566218

    Another thought. If you are trying to connect to a data source through a dsn or something similar, make sure this is still on the server as runing locally will use your client.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 16, 2005 at 11:24 am

    #566221

    It is not a text file, but another server's database.

    When it works, I do not run it though a client, but directly on the server.

    The only time the task fails is when I run it as a scheduled task.

    All my other DTS packages run fine.

    Live to Throw
    Throw to Live
    Will Summers

  • June 16, 2005 at 4:52 pm

    #566275

    Will, how about schedude your task on the server???

  • June 17, 2005 at 2:45 am

    #566336

    Will. See if you can run a simple select statement from the other server in a scheduled package. If not, then its more likely a permission problem on the server against the sql server agent account.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 17, 2005 at 8:53 am

    #566435

    From looking at the error this is bad data, I have had this issue in the past where the job would not run in a scheduled mode, but would run if I manually ran it.

    Take the existing job that is scheduled and delete it.  Rebuild the scheduled job under a different name.

    What user is currently, activating the job?

    Another item to check is to see if the process is running any command or extended procedures from the master database. 

     

  • June 17, 2005 at 9:20 am

    #566461

    The scheduled task runs about 4-5 DTS jobs, each is a seperate step in the job. 

    The one that fails is the second one.

    I can run the job from the 3rd step and it runs fine.

    All DTS steps are pulling from the same server.

    The first 3 step are just pulling small tables off the server - less than 15 seconds to pull whole table.

    Live to Throw
    Throw to Live
    Will Summers

  • June 17, 2005 at 9:41 am

    #566484

    One last throw of the dice for you. Check to see if the package that is failing is logging the execution result to the server in a different way to the rest??

    If not, I would suggest dropping and recreating as stated by someone in their post.

    ------------------------------
    The Users are always right - when I'm not wrong!

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

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