Local Packages into DTS Packages | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Local Packages into DTS Packages

I hope this is the correct forum for this post. I’ve been asked to do the following by my supervisor. He has an access 2000 database on a network share that is updated regularly. He wants to keep this database where it is at, but wants the data posted on our intranet site. I’ve been working on this for a few days now, and have created local packages under data transformation in SQL 7.0 that accomplish this task. My problem begins when I try to schedule the tasks either under the schedule pacakage under the local packages, or under the SQL Server Agent jobs. The package just fails without giving any explanation as to why it fails. If I just execute the package under the Data Transformation local package, it succeeds. I’ve done some searching on the web, but haven’t found anything that really applies, and haven’t found a way to get more information on the failure. Anyone have any ideas or helpful hints. The fact that it works when I select execute but fails when it is scheduled is something of a mystery at this point, at least to me.
Refer to the other posthttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6933 from you about handling DTS package via scheduled jobs. Kindly do not duplicate the posts with same problem, it will be hard for you to track. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
The reason that it fails (most likely) is that the server SQL Server is installed on does not have Office installed so it does not know how to interact with packages that reference Access or that the windows account SQL Server Agent is running on does not have the appropriate permissions to the network shares. It will work on your computer when you execute the package because it is using your credentials and also the resources on your computer. I’m not sure what exactly your package is doing, so I might be off base, but it’s worth checking into.