Post reply

Upgrade/Compatibility level 8.0/DTS Packages

  • May 22, 2007 at 3:07 pm

    #120155

    Has anyone tried to upgrade SQL Server 2000 to SQL Server 2005 (Compatibility level 8.0) so that the DTS packags can be used without changing.  Could you please tell me on how to set this up ?  How do we access the DTS packages for maintenance ? 

    Do we run the SQLServer2000 or the Enterprise manager on the same box as SQL Server2005.  Any information is much appreciated !

  • May 22, 2007 at 3:28 pm

    #708199

    I found the answer to my question.  Please let me know if anyone has tried out yet.

    Microsoft SQL Server 2000 DTS Designer Components

      The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.

  • May 22, 2007 at 3:30 pm

    #708200

    The first thing you'll need if you want to make changes to DTS packages in SSMS is the DTS Designer components.  Here's the download location:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

    I saved the DTS packages to structured storage files from Enterprise Manager in the SQL 2000 instance then opened them in SSMS in the SQL 2005 instance and saved.  In SSMS, expand Management, Legacy, right-click on Data Transformation Services, Import Package File.  If you've downloaded and installed the legacy DTS Designer components, you'll be able to right-click on a package and select Open.

    Hope this helps.

    Greg

    Greg

  • May 22, 2007 at 4:26 pm

    #708218

    Thanks for the info Greg !

    For using the existing DTS Packages: Do you know if we can upgrade to compatibility level 9.0 or only go upto compatibility level 8.0.  - Thank You !

  • May 24, 2007 at 10:38 am

    #708716

    It depends on what the packages do.  I have packages saved as legacy DTS packages in an instance where all the databases have been changed to comp. level 9.0 and they seem to work fine.  Most of them move data from SQL 2000 instances or from an ODBC data source.

    Greg

    Greg

  • May 24, 2007 at 2:02 pm

    #708776

    I have legacy DTS packages doing all kind of weird stuff running fine against 9.0 level databases.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply