SQL Server Performance

DTS jobs all failed

Discussion in 'SQL Server DTS-Related Questions' started by simflex, Oct 22, 2003.

  1. simflex New Member

    First of all, I used dts to migrate data from access database to sql server database.
    Then I went back to dts to schedule a job to append data to the existing tables.
    All the scheduled jobs have failed with the following error message:
    There is already an object named 'Calculations' in the database.
    One of the tables being scheduled is called Calculations.
    I was under the impression that when you schedule a job to append data to existing table, these kinds of problems should not exist.
    What am I doing wrong, please advise.
    Thanks in advance
  2. satya Moderator

    In the dts package that table is created during the execution of the DTS package, ensure that the table is dropped or recreated during package execution.

    Also check whether you've marked to append the data from Access.
    Refer to this KBAhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to go thru about scheduling DTS packages.

    HTH



    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. simflex New Member

    Do I need to delete the the jobs that have failed and re-schedule them?
  4. satya Moderator

    It would be better if you can delete and reschedule, make sure you've specified the options referred above.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. simflex New Member

    In your advise:

    ensure that the table is dropped or recreated during package execution

    Do I need to go someplace else to schedule these jobs because I don't see a feature to drop and recreate table.
    Thanks for your invaluable advise.
  6. satya Moderator

    While creating the DTS package after you choose destination database and corresponding table on the next screen 'Select Source tables' click on the transform button which opens up 'Column mapping and transformations' and under the column mapping ensure 'drop and recreate destination table' is ticked to refer the above text.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. simflex New Member

    Ok, I feel better now that all along, I was at the right place.
    Problem is that that option (drop and recreate destination table is grayed-out (disable).
    Do you happen to know why?
  8. simflex New Member

    Ok, wait!
    If I click on any check box other than the 'append data to destination table' checkbox, that option becomes available.
    Should I select the create destination table option then?
    That was what I was advised against when I first did this.
  9. satya Moderator

    True. follow as found and keep going.
    If the table is created for the first time and data must be appended means ensure 'create destination table option' is not selected and also 'drop and recreate destination table' is not marked.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  10. simflex New Member

    Satya, thank you very much for your assistance.
    This was exactly what I did last time, yet it failed.
    I will try it again and hopefully, it will work this time.
  11. simflex New Member

    Hi Satya,
    I am still having problems - of a different sort.
    After scheduling this job to run at a specified period, I get following error.

    The Microsoft Jet database engine cannot open the file '\ffpc$Program Fileswts CopProfile300 Mons.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

    What is puzzling to me about this is that I used same dts to migrate data from same source into sql server.
    I used dts to migrate same data just to ensure I permission has not been reset but no luck.
    Please advise.
  12. simflex New Member

    I have resolved it.
    Since sql server dts is pulling data from the userr's desktop, we needed to raise the permission level of the box that is being pulled from to the level of sql server and sql server agent.
    Now it works.
  13. satya Moderator

    Thats what I referred the KBA above to make sure the SQLAgent does have necessary privileges.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page