November 1, 2007 at 5:14 pm
After using the 2005 DTS migration wizard, my 100's of DTS packages can only be found in the msdb.dbo.sysdtspackages90 table. I don't see them under Management/Legacy/Data Transformation Services in the Management Studio. I've install the DTS Designer from the feature pack, but since my migrated DTS packages aren't under Management/Legacy/Data Transformation Services I don't know how to run the DTS Designer. Anyone have a suggestion or direction?
November 5, 2007 at 5:52 am
Bob,
I have the same issue. This is what i wanted to do:
1 Active live system is currently running on sql 2000 sp4, server a
2 Proposed new live system is setup with sql 2005 sp2, server b
3 migrate packages on server a to server b
Tried to do step 3 on server b (management/legacy/dts/right click/migration wizard); all steps succeeded. When checking under management/legacy/dts no packages are visible.
Ok, let's run a query on the sysdtspackages table. No packages are in it. ...
Other try out, I ran the migration wizard and used a structured storage file to import a file from server a. This works. Aha let's check.
Select * from sysdtspackages. The package is in it........
Needed to know where the first packages went to ???? Apparently they got into the sysdtspackages90 table in the msdb database.
Does this mean that packages which have been migrated by option1 will never be visible under management/legacy/dts ?? The imports thru structured storage files are ?
If yes, this means that i have to save all packages one after one..... as a structured storage file.....
Would like to do an export thru a wizard at once.....
In my opinion last week all exported packages were visible and now they don't. In that period I installed sp2 , might this be the reason why they are invisble ?
thanks in advanc
November 5, 2007 at 7:28 am
Bob,
What I tried to do now is an export from server a to server b directly. So on Server a I scrolled to 'data transformation services'; right click , export data, all tasks , export data.
Microsoft ole db provider for sql server
server: server a
database: msdb
next
Microsoft ole db provider for sql server
server: server b
database: msdb
next
use a query to specify the data to transfer
next
select * from sysdtspackages
next
destination: sysdtspackages / previeuw
run immediately
Packages show up under server b sql 2005 legacy / data transformation services
Don't know if this is the correct way, but is the result the same as an export of the import of a structured storage file
Anyone has got an advise
November 5, 2007 at 3:46 pm
Seems you're one step ahead of me, I tried your export route and it didn't work, not sure where in space the package landed, but it wasn't on my destination 2005 server as requested. No error indication but it wasn't in either the sysdtspackages or the sysdtspackages90 table.
There must be some success stories out there somewhere, getting DTS packages to 2005 can't be all that rare.
November 6, 2007 at 4:01 am
If you dont want to pull each of the packages in individually & the SQL 2000 box is still online, you can use BI Studio to migrate the packages as a single step. Then you can deploy to SQL 2005 using the deployment manifest.
Catherine Eibner
cybner.com.au
December 19, 2007 at 12:13 pm
Thanks for the input, what exactly is this 2005 deployment manifest you're referring to, or where can I find description?
December 19, 2007 at 2:32 pm
You may have discovered by now that the Migration Wizard is for converting DTS packages to SSIS packages, not for moving them to a SQL 2005 instance as legacy DTS packages. That's why your packages were in sysdtspackages90 and didn't show under Management/Legacy in SSMS.
My favorite method for moving DTS packages intact from SQL 2000 to SQL 2005 is to use DTSBackup2000, available free from http://www.sqldts.com/ to save all the packages in an instance as files, open them in SSMS of the 2005 instance under Management/Legacy, then save them to SQL Server.
Greg
Greg
December 19, 2007 at 6:09 pm
Thanks Greg, I was hoping not to (re)handle my 200+ DTS packages individually, but that's what is so great about MS, it creates job security for those of us who like to spend our hours at mindless tasks.
I appreciate your guidance, I guess I'd better get started.
December 20, 2007 at 12:19 pm
I hear you! DTSBackup2000 at least lets you save all the packages to files without having to open each on in DTS Designer. Small consolation with that many packages, though.
Good luck,
Greg
Greg
December 21, 2007 at 3:30 am
Thanks to Greg and Catherine for pointing out how to migrate existing DTS packages from SQL2000 to SQL2005 and keeping them as legacy format.
I had assumed that the Migration wizard was for moving from one server to another and could not find my DTS packages under the Legacy section. Only after much frustration did I find it had migrated them to SSIS format as well.
After much investigation, it had appeared to me that the only way to preserve them as DTS would have been an in-place upgrade. This would not have been practical for my scenario as we are moving from 32 bit to a new 64bit server.
I had dreaded having to ensure the SSIS packages worked given my timeframe to move the live system to the new server this coming week. I can now rely having original DTS packages.
One less item to worry about on Monday.
------
Robert
January 15, 2008 at 11:27 pm
Hi,
Following this, have you successfully managed to schedule your DTS2000?
The only way I've found to do this is using the DTSRun command. However it seems to like only the /Z~xxxxx encrypted details from the SQL2000.
Does anyone know any other way? I've tried the different parameters and it doesn't seem to work. Or does anyone know a way to encrypt the DTS details into the respective string? (painful :crying:).
thanks.
Regards,
Bernadette
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply