How many SQL Server 2000 DTS
packages are you responsible for? Perhaps 10? Maybe 50? When I asked
this question at a conference last year, a
few attendees shocked me by telling me
that their environments boasted more than 2000 DTS packages! Was it irresponsible of me to wish them good luck
in their migrations? Perhaps. But if you
have several hundred DTS packages, you
should know that you’re not alone. DTS
is well known for its simplicity and usefulness, and in fact, many organizations
adopted DTS for its ability to quickly
and simply generate data processing.
Whether you have 10 DTS packages
or several hundred, you’re probably preparing for an inevitable migration to SQL
Server 2005 Integration Services (SSIS).
However, in your testing and research, the
pain points have become apparent: The
product’s architecture differs from that
of SQL Server 2000 DTS—making the
migration more complex than a straight
upgrade—and even after you migrate a
couple packages, you might see only a little
performance gain. And if you’re looking at
dozens or hundreds of packages, you can
expect several sleepless nights.
Your First Step
A good starting framework for a DTS-to-SSIS
migration strategy includes first getting your
DTS packages to SQL Server 2005. Don’t let
your DTS migration hold up your relational
engine upgrade to SQL Server 2005.
- Move your DTS packages to your SQL
2005 environment (through the setup.exe SQL upgrade or manually through
Management Studio), and continue to run
them as DTS packages.
- Use SSIS to build any new packages
you need.
- Use the DTS Migration Wizard as a
starting point for strategic packages that
can take advantage of SSIS features or
for packages that have trouble during the
migration.
- Plan for a rolling strategy to rework
all packages, leveraging the complete SSIS
feature set in the redesign.
There’s a light at the end of the tunnel—a
bright one. And getting there might not be
as difficult as you think. In fact, now that
SQL Server 2005 is more than a year old,
more resources and knowledge are available
to help you through the process. (For a
helpful primer, see “Step Up to SQL Server
2005,” InstantDoc ID 47749, as well as this
article’s sidebar, “Your First Step.” )
But don’t settle for merely getting your
packages migrated to SSIS. Rather, prime
yourself for taking full advantage of SSIS’s
improved features and functionality.
Migration Resources
When you’re ready to take on your DTS-to-SSIS migration, you’ll need to have at
your disposal the tools and resources you
need to make informed decisions and
perform a clean migration. One such tool
is the Upgrade Advisor, which can analyze
your existing DTS packages and report
about concerns that you must address before
and after your migration. The most recent
version of Upgrade Advisor is downloadable with the “Feature Pack for Microsoft
SQL Server 2005 - April 2006” (http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aab4bd-4705-aa0a-b477ba72a9cb
&DisplayLang=en).Even more
valuable is the “SQL Server 2005
Upgrade Technical Reference
Guide” (http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en), which covers
the entire SQL Server platform
upgrade in 350 pages, with 38
pages dedicated to DTS package
migration to SSIS—invaluable to
understanding the nuances of the
upgrade. Both of these resources cover
the basics of using SQL Server’s built-in
DTS Migration Wizard to convert DTS
packages to SSIS. To invoke the DTS
Migration Wizard, you right-click the
SSIS Packages folder in Business Intelligence Development Studio, as Figure 1 shows. (To prepare your packages for the
DTS Migration Wizard, see the sidebar
“Preparing for the Wizard.”)
One of the biggest challenges involved
with migrating DTS packages to SSIS
arises when the DTS Migration Wizard
can’t convert a DTS component, such as
a data pump that does more than copy
column operations. In some cases, the
converted SSIS package will retain a
portion of the original DTS package,
embedded in an Execute DTS Package
task in SSIS. Other tasks, such as the
Dynamic Property Task and some of the
logic embedded in an ActiveX task, will
need to be rewritten with SSIS functionality. Each of the migration nuances will be identified by the Upgrade Advisor tool
or discussed in the upgrade planning and
preparation resources.
Beyond just the migration resources
that SQL Server 2005 provides in the DTS
Migration Wizard and Upgrade Advisor,
the software also includes support for
DTS packages. For example, the \Management\Legacy folder in SQL Server 2005
Management Studio (SSMS) includes a
container for DTS 2000 packages, as you
can see in Figure 2. These packages might
have been included with the SQL Server
relational engine upgrade; you can also load
DTS packages after the upgrade into a SQL
Server 2005 instance.
With the DTS design tools installed for
SSMS (available in the “Feature Pack for
Microsoft SQL Server 2005 - April 2006”
referenced earlier), you can modify DTS
packages through SSMS and execute them
on the server. Also, using the Execute DTS
2000 Package Task included in the SSIS
Control Flow, you can run DTS packages
alongside SSIS packages, so you can move
forward with SSIS packages while managing
your migration incrementally.
Prev. page
[1]
2
3
next page 
