> M A R C H 7 , 2 0 0 7
|
Maintaining your SQL Server 2000 DTS Package in SQL Server 2005 Business and Technology Corner
Maintaining your SQL Server 2000 DTS Package in SQL Server 2005 by RDA Senior Software Engineer Joe Toscano
Let’s assume you are supporting a customer that wants to upgrade from SQL Server 2000 to SQL Server 2005. There are many reasons to justify this decision as outlined in the following excellent white paper:
http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx
What if your customer has hundreds of SQL Server 2000 DTS Packages in their environment that took years to design, deploy and stabilize? Further, what if you know these are complex packages that, for reasons discussed later in this paper, will not be easily upgraded from DTS to SSIS. Do you put off their database upgrade plans because the daunting DTS to SSIS migration task is something that they are not ready to undertake?
This article looks at a way you can edit, maintain and even continue developing your existing SQL 2000 DTS packages using the SQL Server 2000 DTS Designer Tool until these DTS packages can be upgraded to SSIS packages. We will see how this design tool works within a SQL 2005 environment. We will discuss some typical DTS to SSIS migration challenges and we will see how these ‘challenging packages’ can be identified. Finally, we will look at a SQL 2000 to 2005 upgrade path for our sample customer and provide the rationale for choosing to preserve our DTS environment.
The DTS to SSIS Migration Challenges
The SQL Server 2005 Upgrade Advisor Before performing an upgrade to SQL 2005, it’s highly recommended that you utilize the Microsoft SQL Server 2005 Upgrade Advisor to scan not only your databases but also your DTS Packages. The Upgrade Advisor doesn’t make any changes. It scans and identifies feature and configuration changes that might affect your upgrade. The Upgrade Advisor then provides links to documentation that describes each identified issue and how to resolve it. You can use this tool to help scope out your DTS to SSIS migration effort. The Upgrade Advisor can be found at the following site:
We will assume that the Upgrade Advisor pointed out our migration challenges for our customer’s DTS packages. While our DTS packages may contain many incompatible components, a migration path still exists for these targeted packages. The migration path may require some timelier package re-engineering as opposed to a simple wizard-driven DTS to SSIS migration. In the interim, we have made our choice to support our current DTS environment using the SQL Server 2000 DTS Designer tool discussed in our next section. Keep in mind that we have chosen to continue with our database migration because the Upgrade Advisor did not find migration issues with our databases. Let’s take a more detailed look at our SQL Server 2000 DTS Designer.
The SQL Server 2000 DTS Designer The SQL Server 2000 DTS Designer allows you to maintain and even modify your DTS packages from SQL 2005’s Management Studio. (You install it on your SQL Server 2005 server.) The package development environment very closely mimics the DTS Package development environment you may be used to using in SQL Server 2000’s Enterprise Manager. This DTS Designer is part of the freely downloadable SQL Server 2005 Feature Pack and provides you with the ability to edit and save your DTS packages from a SQL Server 2005 environment. A Feature Pack overview along with download requirements and instructions can be found at the following site:
While the SQL Server 2000 Designer is for maintaining and developing DTS packages, the Business Intelligence Development Studio is used to develop and maintain SSIS packages. Since we have made our choice to perform the upgrade of our SQL Server databases and to preserve our extensive and complex SQL 2000 DTS environment, let’s take a look at our proposed upgrade plan.
Our SQL 2005 In-Place Upgrade REMOVES SQL 2000 One site I upgraded from SQL 2000 to SQL Server 2005 chose to run both versions of SQL Server, SQL 2000 and SQL 2005, side-by-side. (Several of their 3D party applications were not ready to support SQL 2005) Doing this was both practical and possible because they were a low-volume shop with very small scale databases. By performing a side-by-side upgrade, we were actually able to preserve our SQL Server 2000 development environment so we didn’t need to download the 2000 DTS Designer. In many cases, this type of upgrade may not be practical or even possible. Instead, an in-place upgrade may be a preferred option. An in-place upgrade essentially overwrites your SQL 2000 instance with a 2005 instance. If you choose this upgrade path, your databases are replaced with their 2005 equivalents and your development tools are replaced with 2005’s new tools. We will assume that this is the case with our large-scale high-volume customer and that we will no longer have the SQL 2000 designer tools such as Enterprise Manager at our disposal once the upgrade is complete.
Our Post-Upgrade State of the Union: Where did our old DTS Packages go? After you perform an in-place upgrade of SQL Server 2000 to SQL Server 2005, your DTS Packages are NOT automatically upgraded. Instead, your non-upgraded DTS packages and jobs are carried over to the SQL 2005 instance in place. You will be able to find these 2000 packages in SQL Server 2005 Management Studio under Management > Legacy > Data Transformation Services. You can continue to run these packages along with the jobs that kick-start them since the DTS runtime environment carries over; however, you will not be able to modify these packages without installing the SQL 2000 DTS Designer. Assuming you installed the 2000 DTS Designer, you can open and then modify your packages by right-clicking on them and choosing Open.
Conclusions Long-term support for SQL Server 2000 DTS may not be in cards; therefore, it may be wise to consider using this release of SQL Server 2005 to upgrade your DTS Packages to SSIS. The SQL Server 2000 DTS Designer can be used to give you the time you may need to meticulously plan for the eventuality of a DTS to SSIS upgrade project.
When you are ready to upgrade your DTS packages, you can use the Package Migration Wizard which can be launched from either Management Studio or the Business Intelligence Development Studio. The Migration Wizard performs a “best effort” migration, meaning it migrates the components that have DTS to SSIS mappings. For the components that cannot be mapped, the wizard encapsulates these tasks or features in an Execute DTS 2000 Package Task. These legacy remnants should be the tasks that you focus on re-engineering to take advantage of SSIS’s rich set of features such as Containers or Package Configurations. In many cases, tasks that required custom code under DTS have pre-baked existing feature support in SSIS. Here, knowledge of SSIS’s stand-out features and capabilities will prove invaluable.
Caveats The SQL Server 2005 Upgrade Advisor has a number of SQL Server specific requirements, operating system and service pack requirements, .NET framework and Windows Installer requirements.
The SQL Server 2005 Upgrade Advisor and the Package Migration Wizard can only scan DTS packages that reside in the Repository if the SQL Server 2000 tools (including the Repository redistributable files) are installed on the local computer. SQL Server 2005 does not install or use the Repository.
When you select Integration Services for installation, setup also installs support for DTS packages including the DTS Runtime. If you upgrade the last instance of SQL Server 2000 on the computer to SQL Server 2005, registry entries are removed that are required by DTS. These can easily be restored by running the Repair of “Microsoft SQL Server 2005 Backward Compatibility” Setup wizard.
|
Business and Technology Corner Recommended Reading The Windows Communication Foundation is the framework in .NET 3.0 for building service-oriented applications. This article steps through the essentials of building and configuring a WCF service.
Building Business Processes in Windows Workflow Foundation The Windows Workflow Foundation lets you design workflows for business processes and then build apps from them. In this example, the author designs a console app in the workflow designer that prompts the user for a username and password.
ASP.NET 2.0: Manage Web Users With Custom Profile Providers With ASP.NET 2.0, you can add authentication, authorization, and profiles to your Web site without writing a single line of code.
Process Recycling and Its Triggers Automatic Process Recycling mops up memory leaks in long-running ASP.NET apps. This article reviews some typical causes of ASP.NET app restarts.
Developers and architects can choose from a variety of programming models to find the most effective way to bring AJAX into new and existing apps.
InformationWeek's Windows Vista Roundtable, Part
Four
No Turning Back From Rich Internet Apps
About RDA RDA provides custom application and software development services for all types of companies. Our experienced staff employs frameworks and a proven process to create business solutions that help our clients reduce costs and improve results.
RDA specializes in .NET and Java solutions in the areas of business intelligence; communication and collaboration; enterprise application integration; mobility; operational efficiency and productivity; portals; and security.
RDA and Microsoft RDA is a Microsoft Gold Certified Partner in Business Process and Integration, Custom Development (Web and Application Infrastructure), Data Management (Business Intelligence), Information Worker, and Mobility solutions. RDA has been named Microsoft's 2005 Partner of the Year for the Southeast District and 2004 Partner of the Year for the Mid-Atlantic District. RDA is also a participant in Microsoft’s Virtual Technology Specialist Program, a go-to partner for Microsoft’s Enterprise Service Bus offerings, and a winner of the 2003 Microsoft Partner Program Integration Solution of the Year.
RDA has developed innovative technology solutions for a broad and diverse client base since its founding in 1988. Detailed case studies organized by industry, solution and technology type reside on our corporate website at www.rdacorp.com.
Headquartered in Baltimore, RDA also has offices in Atlanta, Charlotte, Philadelphia, Seattle, and Washington, D.C.
RDA has designed a series of educational workshops and seminars to provide you with the information you need to be current, relevant, and effective. Our team of technical specialists present sessions focusing on specific business technologies and products. Workshops may be targeted to general audiences or developed specifically for your company. To learn more about the events available to your company, please visit RDA's events page on our corporate website.
For additional information about events, please contact Joe Klein at 410 308-9307 or at jklein@rdacorp.com.
About the Spotlight RDA Spotlight on Business and Technology is an email newsletter published by RDA to benefit our clients.
If you would prefer to be removed from our subscriber list, please send an email to listadmin@rdacorp.com with the following text in the subject line: Unsubscribe “RDA Spotlight”
Contact Us To reach someone at RDA, contact a member of our sales team at Sales@RDACorp.com.
© 2007 RDA Marketing. All rights reserved.
|
