Problem moving DTS pkg from one server to another | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem moving DTS pkg from one server to another

I’ve been having a problem while moving a DTS package from one server to another and then calling the DTS package from a job. I have a development server and a staging server. The database name on development is db_namedev. I have a package that takes data from that database and exports it to a text file. If I execute the package from the DTS designer screen it executes properly and if I call it from an SQL job it also functions correctly. After that, I wanted to move the job to my staging environment. The db name on the staging environment is different from that in development (I know, bad idea – but that’s the way it is for now). So, I went into the package, and changed the datasource server and database name. I saved the package on the staging environment and executed it from the DTS package designer screen. It ran perfectly. However, when I set up a job on the staging server to run it, it failed. The error message said that the table [db_namedev].dbo.tablename did not exist. At first I thought that maybe I forgot to change the dbname in the datasource and in the transform task. However, after checking, I found that they are represented correctly. And that’s how things stand. I can execute the package properly from the DTS package listing. However, if I put it in a job and call it using the command-line dtsrun executable, it "reverts" back to the development server information. Why is that? Does anyone have any ideas? Thanks in advance for your help. Zev Steinhardt
Review about moving and dealing DTS packages. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Check this