DTS jobs all failed | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS jobs all failed

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

Do I need to delete the the jobs that have failed and re-schedule them?
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

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

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

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

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.
Thats what I referred the KBA above to make sure the SQLAgent does have necessary privileges. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>