July 13, 2009 at 2:08 pm
We converted scheduled jobs from 2000 to 2005. Some of these jobs run DTS packages. Where are the converted DTS packages stored and how can I view and modify them?
Dave
July 13, 2009 at 6:15 pm
You need to install the backward compatibility tools from the SQL Server 2005 Feature pack to see them, but they still exist in msdb, you just can't see them..
CEWII
July 14, 2009 at 11:49 am
Thanks. I installed the backward compatibility tools and I can now see/modify one of the DTS packages that I transferred to the new server. It shows up under management/legacy/DTS in SQL server management studio. However, the utility that converted the scheduled jobs seems to have automatically converted the other DTS packages and I don't see those under Management/legacy/DTS. The jobs run but I have no idea where the packages are. I could probably transfer the other DTS packages so I can see them but I'd really rather know where they are.
July 14, 2009 at 12:22 pm
To be more accurate, I installed the 2000 DTS designer components. I just installed backward compatibility components (on my client) and I'm still not seeing the packages.
Thanks
July 14, 2009 at 12:40 pm
Transferred the jobs using a "transfer job task" in visual studio.
July 14, 2009 at 2:19 pm
Dwolfe, Sorry I got confused here. Since I haven't done this on my own. SO you telling me that there were jobs in Sql 2000, some of which used to run your dts packages in SQL 2000.
Then you used Transfer jobs task in SSIS to move jobs from SQL 2000 to SQL 2005, which also includes your dts packges. SO my question is "when you transferred jobs that is executing your dts 2000 packages, do that get upgraded as well to SQL 2005? I got this impression from your posting. My believe is your dts packages just stays as 2000, but since you have install your backward compability you ca run it with 2005 and even edit if u have edit component install.
I think you can just go to the jobs and see where the jobs is pointing for packages in 2005.
If it is in msdb, SELECT * FROM msdb.dbo.sysdtspackages90 will give you all the packges.
July 14, 2009 at 3:03 pm
Addicted,
You have accurately described what I did.
Transferring the jobs also seems to have transferred the DTS packages that those jobs run. Jobs that kick off DTS packages work. However, if I want to change one of these DTS packages, I can't because I can't see it anywhere in SQL Server Management Studio. When I look at the job step definition I see something like
DTSRun /~Z0xCCC427D187815FCBC3256AAC3AB21AB00580...
On one of these DTS packages I went back to 2000, opened the package, selected "save as" and saved to the 2005 server. I can now see that package in Manangement Studio and edit it with my newly installed DTS backward compatibility stuff. I can do the same thing with all of the DTS packages as a work around but I was really hoping to find the secret place where these transfered packages live!
Thanks,
July 14, 2009 at 3:18 pm
Addicted,
You have accurately described what I did.
Transferring the jobs also seems to have transferred the DTS packages that those jobs run. Jobs that kick off DTS packages work. However, if I want to change one of these DTS packages, I can't because I can't see it anywhere in SQL Server Management Studio. When I look at the job step definition I see something like
DTSRun /~Z0xCCC427D187815FCBC3256AAC3AB21AB00580...
On one of these DTS packages I went back to 2000, opened the package, selected "save as" and saved to the 2005 server. I can now see that package in Manangement Studio and edit it with my newly installed DTS backward compatibility stuff. I can do the same thing with all of the DTS packages as a work around but I was really hoping to find the secret place where these transfered packages live!
Thanks,
I don't think transferring jobs will also upgrade your package into 2005. Since you saved one of the 2000 dts package as save as inot 2005 , u did an auto upgrade ( or some sort of change) so that u can see it. I think you have to manually change or upgrade all dts package. I might be wrong.
Where were ur dts packages in 2000? file or msdb?
On job step when u click there is place where you see where the location of the package is. If it is in msdb you will see server inofrmation , if it is in file system, it will be the full path where the package is.
I don't know where u got above value. But when u create a SSIS package and in the step, there is a option where it asks for ssis package location which will be ur loaction of the pacakge.
July 14, 2009 at 3:27 pm
As far as I know, the Transfer Jobs task doesn't copy DTS packages, so I'm kind of confused by your problem. Is it possible that some of the packages were copied from SQL 2000 some other way?
The best way to copy DTS packages from a SQL 2000 instance to a SQL 2005 instance is to save the packages as files, then import the package file in SSMS. DTSBackup2000, a free download from http://www.sqldts.com/242.aspx, makes moving multiple packages easy. By the way, DTS packages that are stored in msdb are in msdb.dbo.sysdtspackages.
To decrypt a DTSRUN command as used in a job step, copy the command to a cmd prompt, append /!X /!C to the end, and run it.
The !X argument blocks execution of the package and the !C argument copies the unencrypted command to the Windows clipboard.
Just open Notepad and paste to see the unencrypted command.
Example:
DTSRun /~Z0x5F473BFAEEC0EE48EAF6121A88792F925A32FAF162A18E19FB06C5246A4CA2785EE38A17F0A6101B17B16C8F336C65EBF62B0E624C1E9B83 /!X /!C
Greg
July 14, 2009 at 3:29 pm
2000 dts packages are msdb.
DTS jobs are of type "Operating System Command".
I don't think any of the DTS packages were converted to SSIS. I just think they were transfered and still run because there is some backward compatibility. That's my uneducated guess.
July 14, 2009 at 3:34 pm
I think Greg answered the issue, and in 2005 ssis packages are in msdb.dbo.sysdtspackages90.
I think you have to take out the dts package from msdb and save and you should to able to see and view packages.
July 14, 2009 at 3:48 pm
I feel a bit dumb here but thanks to Greg's suggestions I've discovered that the packages are being executed from the jobs on the 2005 server but appear to be running on the 2000 server.
Thanks for your help!
July 14, 2009 at 3:53 pm
I think what a fool i was too not to tell you that dts packages were in 2000.
There was no doubt that packages were in 2000 msdb. It's just job is pointing to that server, and since u have install backward comptbility , u are able to run and see dts packages.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply