Upgrade/Migrate DTS

QPR
QPR used Ask the Experts™
on
Surely this should be simple.
I have a basic DTS package in SQL2000 which truncates some tables and then fills then with data. The data (source) is a simple query and the pump is a simple copy colum.
I ran dtsmigrationwizard.exe, specified the source (SQL2000) and the destination (sql2005) and checked the box against the package it found. When I click next/finish I get an error. Don't seem to be able to copy from the error dialogue so, below is the contents of the log file.
My windows account is SA on both source and destination servers.

LogID=1
#Time=4:39 p.m.
#Level=DTSMW_LOGLEVEL_WARN
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Not all the connections from SQL Server 2000 are migrated to the main SQL Server 2005 package. For example, Text (Source) and Text (Destination) are not migrated to the main package.

LogID=2
#Time=4:39 p.m.
#Level=DTSMW_LOGLEVEL_WARN
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=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.

LogID=3
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Finished migrating connections for package: Copy PG to WDC Orgchart

LogID=4
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Started DTSExecuteSQLTask task migration for package: Copy PG to WDC Orgchart

LogID=5
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=   at DTS.Task.get_CustomTask()
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)

LogID=6
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Started DTSDataPumpTask task migration for package: Copy PG to WDC Orgchart

LogID=7
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=   at DTS.Task.get_CustomTask()
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)

LogID=8
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Started DTSDataPumpTask task migration for package: Copy PG to WDC Orgchart

LogID=9
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=   at DTS.Task.get_CustomTask()
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)


LogID=10
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Started DTSDataPumpTask task migration for package: Copy PG to WDC Orgchart

LogID=11
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=   at DTS.Task.get_CustomTask()
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)

LogID=12
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_MSG
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=User terminated migration.

LogID=13
#Time=4:40 p.m.
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException: Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). ---> System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'DTS.CustomTask'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{10020904-EB1C-11CF-AE6E-00AA004A34D5}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
   at DTS.Task.get_CustomTask()
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.ReportError(Exception ex, String errorText, String packageName, String packageEllement)
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
   at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
If the DTS package is that simple, just create it from scratch in SSIS.  I suspect that would be a lot quicker than trying to coerce DTS Migration Wizard to work.

Alternatively checkout DTS xChange at:
http://www.pragmaticworks.com/
QPR

Author

Commented:
I would... had I found the time to get stuck into SSIS yet!  :)
Is the migration wizard really that bad that it can't handle

truncate table X
copy from server 1 table Y into server 2 table X
(straight copy column, no ActiveX massaging along the way)
Top Expert 2012

Commented:
>>I would... had I found the time to get stuck into SSIS yet! <<
I get the impression you have already spent way too much time on it.  Even without prior experience and provided there are not too many columns, I suspect it would take you all of 30 minutes to create it.

>>Is the migration wizard really that bad that it can't handle<<
I honestly do not know, I have never used it, but the reviews are not kind.  See here from Allan Mitchell:
DTS to SSIS Migration
http://www.simple-talk.com/sql/ssis/dts-to-ssis-migration/

But if I were to use it, I would first attempt it on a DTS package with a single transform.  Then you can isolate if it is your environment or the actual Wizard.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012

Commented:
Also check out DTS xChange, I understand it has a demo.

And finally there are a couple of intermediate options:
1. Continue to maintain/execute the DTS Package from SQL Server 2005.
2. Execute the DTS Package from an SSIS package using the Execute DTS 2000 Package Task.
QPR

Author

Commented:
ok you did't answer the Q but your non-answering forced me to get off my butt and learn how to do it in SSIS (long overdue) which I've now done and it all works fine :)
Top Expert 2012

Commented:
>>ok you did't answer the Q but your non-answering forced me to get off my butt and learn how to do it in SSIS<<
Actually you did exactly what I suggested in my first comment:
"If the DTS package is that simple, just create it from scratch in SSIS."
QPR

Author

Commented:
Yep that's what I meant.
I should have written "didn't solve the problem"  "translate the error" "fix my upgrade process or point me in right direction"

Give a man a fish, teach a man to fish etc

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial