Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Upgrade SQL Server DTS Packages to Integration Services Packages


By:   |   Updated: 2007-04-17   |   Comments (9)   |   Related: More > Integration Services Upgrade

Problem
With as many Data Transformation Services (DTS) Packages that have been developed and deployed for B2B, data integration and BI needs, when it comes to upgrading from SQL Server 2000 to 2005, this portion of the upgrade will need special attention.  DTS Packages have become engrained in many applications and business processes making them business critical for not only internal applications but also mission critical for business partners.  In addition, DTS Packages are probably being used in unexpected ways than originally intended further complicating the upgrade process.  In some respects DTS Packages being called directly from web pages or being automatically triggered based on a business event follow a much different paradigm than those DTS Packages being called from a SQL Server Job as was probably how DTS Package execution was originally intended.  With the varying usage of the SQL Server 2000 DTS Packages, what is the upgrade process to SQL Server 2005 Integration Services (SSIS) Packages?

Solution
The DTS Package (SQL Server 2000) to SSIS Package (SQL Server 2005) upgrade is dependent on the Business Intelligence Development Studio which follows the Visual Studio paradigm of solutions and projects.  In the example below, we will create a single solution and project with a single SSIS Package, although numerous SSIS Packages can reside in a single solution.  Follow these steps to migrate your DTS Packages to SSIS Packages with the Migrate DTS 2000 Package Wizard:

ID Steps Screen Shot
1 SQL Server 2000 DTS Package - The original logic is to delete data from the destination table and then import all of the data.

2 Migrate DTS 2000 Package - Start the migration wizard by starting the Business Intelligence Development Studio, creating an Integration Services project and navigating to Project | Migrate DTS 2000 Package.
3 Choose Source Location - Specify the SQL Server 2000 server name with the DTS Package that needs to be migrated and the authentication type.

Press the 'Next >' button to continue the process.

4 Choose Destination Location - Directory on your desktop to save the SSIS Package file.

Press the 'Next >' button to continue the process.

5 List Packages - All DTS Packages on the SQL Server 2000 instance that can be upgraded.

Press the 'Next >' button to continue the process.

6 Specify a Log File - Press the 'Browse' button to specify a log file for the migration process.

Press the 'Next >' button to continue the process.

7 Complete the Wizard - Review the configurations and then press the 'Finish' button to begin the migration process.
8 Migrating the packages... - Review the status for the migration.
9 Integration Services Package - Review the objects to validate the code was successfully migrated.

For information on deploying SSIS Packages, reference - SQL Server Crosswalk - Deploying a SQL 2000 DTS vs. a SQL 2005 SSIS package.

Next Steps

  • As you begin to think about upgrading to SQL Server 2005, download and execute the SQL Server 2005 Upgrade Advisor to identify all potential upgrade issues as you begin the project.  Be sure to analyze your DTS Packages, review, correct and test each of the DTS Package issues, then re-run the Upgrade Advisor to ensure a smooth upgrade to SQL Server 2005.
  • Since SQL Server 2005 has moved to the Visual Studio paradigm of Solutions and Projects, be sure to setup them up for your SSIS Packages in the Business Intelligence Management Studio (BIDS).
  • As you architect and implement your Integration Services infrastructure, keep in mind that it is a separate installation in SQL Server 2005.  Depending on the number of SSIS Packages and the processing power needed, consider setting up a separate SQL Server instance for Integration Services.
  • Check out these related MSSQLTips.com tips:


Last Updated: 2007-04-17


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 28, 2014 - 3:17:38 AM - harish Back To Top

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)).
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue with the next task.

when i migrate i am getting following error


Monday, September 16, 2013 - 6:10:09 AM - Kumar Back To Top

Hi Jeremy,

     Can u pls help me on the below error.   

Messages

* 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)). (Microsoft.SqlServer.DtsMigration)

------------------------------

ADDITIONAL INFORMATION:

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)). (Microsoft.SqlServer.Dts80)


Wednesday, July 17, 2013 - 9:55:47 AM - Ramon Back To Top

Hi Guys.

I will be able to have the packages on the original server sql2000 i just wnat to mave the packages in case of a hardware issue. 

Thank you


Monday, July 08, 2013 - 3:04:21 PM - Harini Back To Top

Hi Jeremy,

 

It helped me a lot.

 

Thank you so much,

Harini


Saturday, June 29, 2013 - 6:08:45 PM - Jeremy Kadlec Back To Top

Robin,

Is this an issue with a single package or all packages?  Do these packages have script tasks?  If so, can you review the code and see what the code is doing, connecting to, etc.?

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, June 28, 2013 - 8:46:19 AM - Robin Back To Top
Hi Jeremy,
I'm trying to migrate our DTS 2000 to 2008R2.
Until point 7 it's OK.
Unfortunately at point 8 I get the following message :
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’{10020…..}’ failed due to the following error : No such interface 
Supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Bye.
Robin

Wednesday, April 17, 2013 - 2:33:53 PM - Jeremy Kadlec Back To Top

Shelley,

Great!  That makes my day.

Check out these additional SSIS resources - http://www.mssqltips.com/sql_server_business_intelligence_tips.asp.

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, April 16, 2013 - 11:51:09 PM - shelley Back To Top

Hi, Jeremy, this document helps me a lot. Thank you very much.


Wednesday, April 02, 2008 - 6:28:24 PM - binaryworld Back To Top

If anybody serious about DTS to SSIS Migration ..... 

Here is the tool called DTSxChange to help you with DTS to SSIS Migration / Conversion Projects. Very easy to use and converts thousands of packages. They offer great price fom small companies who have less than 50 packages.

http://www.dtsxchange.com/


Learn more about SQL Server tools