Migrate from SQL Server 2000 DTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Migrate from SQL Server 2000 DTS

Is it going to be possible to migrate SQL server 2000 DTS to SQL Server 2005 or would it have to all be rewritten?

It’s possible to migrate. There’s a migration utility that will be included; however, it won’t be perfect. The easiest conversion method will just wrap the entire existing DTS as a VBScript and store the script. So, you’ll lose the current GUI until you go through a full conversion process or use the utility. The stuff will still run though. You might want to get clarification on this from the MS newsgroups. It’s been a while since I discussed with someone for MS, so it might have changed. The product is still in development. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
FYI. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
It depends on what you packages do.<br /><br />The migration utility is called SSIS Migration Wizard, but some DTS tasks don’t map to SSIS tasks<br /> (custom, sql server, copy database, and data pump tasks for example) but you should be okay with execute, bulk inserts, message queues, send mail, and FTP stuff.<br /><br />Most places I know haven’t got a clue as to what their packages do any way, so I’d suggest trial and error, and a good deal of testing <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Over the weekend I have successfully managed to migrate SQL 2k DTS tasks to SSIS without any hurdle!
I was expecting some problem with the user accounts used in the previous package, but it went thru without any issues… I’m liking SSIS though. Satya SKJ
Contributing Editor & Forums Moderator
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
This is the only issue I have run into converting DTS
Not all tasks from SQL Server 2000 and SQL Server 7.0 are migrated to SQL Server 2005. For example, the migration of the Analysis Services Processing task and the Data Mining Prediction task is not supported. (Microsoft.SqlServer.DtsMigration) ——————————
ADDITIONAL INFORMATION: Invalid class string
(Microsoft Data Transformation Services (DTS) Package)
Did you try to "edit" the migrated packages (VS2005 BI-studio) ?
If yes : Did you find your "data-flow"- pane containing data ? I’ve got simple server1 to server2, 1-table to 1-table data-copy packages with 100% column mapping by name.
Aparently this wizard cannot handle these kind of "transformations" and just copies the encapsulated datacopy-step. This will be editable usint the DTS2000-package designer add-on. I was trying to avoid just that. Any ideas on this ?