asked on

Upgrade/Migrate DTS

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)

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

QPR

ASKER

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)

Anthony Perkins

>>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.

Anthony Perkins

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

ASKER

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 :)

Anthony Perkins

>>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

ASKER

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