My high-dollar consultants can get a basic SQL Server 7.0 Data Transformation Services (DTS) job set up and working. But they claim they can run the job only from the computer they built the job on. If they run the job on a different box, all the saved settings have to be reset and resaved. This rework makes no sense to me (but I'm not a SQL Server DBA). Still, after reading through several Q&As about DTS, I wonder whether this problem might be permissions-related. Can you provide any guidance?
Your consultants are partially correct. To run the job from another system, you do need to change the user IDs. So if you develop connection objects by using one set of user IDs, you might need to change these IDs when you deploy the package . However, you don't need to directly edit the DTS package to make these changes. Darren Green's Web site at http://www.swynk.com/friends/green has examples of how to change the user IDs in SQL Server 7.0. In SQL Server 2000, you can use Universal Data Links (UDLs) or the new DTS Dynamic Properties task to change the connections at runtime. For more information about DTS, see the Microsoft DTS newsgroup (on server msnews.microsoft.com, group microsoft.public.sqlserver.dts). You can also find DTS information on the SQL Server Magazine Web site (http://www.sqlmag.com) by browsing the public forums and by searching on keyword DTS.