Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-06-22 : 13:45:14
|
| I have a weird situation. I have migrated some DTS packages from a sql 7.0 server to SQL2005. One of them works as a DTS package but fails under the job sections with an error saying could not connect to the database server. I have no option but to rewrite the whole package in SSIS(which I hate).Any ideas.RegardsParesh MotiwalaBoston, USA |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-22 : 15:30:14
|
| Depends on what the package does. |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-06-22 : 16:18:55
|
quote:
Originally posted by pareshmotiwala
I have a weird situation. I have migrated some DTS packages from a sql 7.0 server to SQL2005. One of them works as a DTS package but fails under the job sections with an error saying could not connect to the database server. I have no option but to rewrite the whole package in SSIS(which I hate).Any ideas.RegardsParesh MotiwalaBoston, USA
Ok, I seemed to have figured it out.When you migrate the DTS package from older versions to the sql2005, it generates a. a DTS Package under managemet/legacy and b. a job under sql server agent.When I first changed the SA password, the DTS package now has the same ID but a different Version ID. Further the changes made to the package do not propagate to the job. Hencea. The SA password had changed and it did not allow me to run it.b. After I figured that out, I found out that the DTSRun command also does not take into consideration the DTSPackage Version ID.c. The values given in the Job are all Hexa not plain text. So by finding out the Version ID of the successfully executed DTS package, I changed the job's command to run under trusted connection and use the correct combo of Package ID and Package VersionID.Hope this saves a few bacons...RegardsPareshRegardsParesh MotiwalaBoston, USA |
|
|
|
|
|
|
DTS Packages executions