Upgrading DTS Packages to Microsoft SQL Server Integration ...

Upgrading DTS Packages to Microsoft SQL Server Integration ...

Upgrading DTS Packages to Microsoft SQL Server Integration Services Brian Knight Founder Pragmatic Works BIN207 About the Speaker Brian is a SQL Server MVP

Founder of Pragmatic Works Co-founder of SQLServerCentral.com and JumpstartTV.com Written ten SQL Server books Trains at End to End Training (http://www.endtoendtraining.com) Failed Ad Campaign

Why Choose SSIS? 64bit support Parallel in-memory multi buffer architecture helps to load data several times faster than DTS Logging, configuration, CheckPoint etc. Source Safe Integration because everything is XML Many new tasks (e.g. Script Task, For Each Loop, XML Task etc.) that replace need for ActiveX

DTS vs. SSIS Engine Speed Test On 32 bit dual core machine Pulling 1 million rows out and writing to SQL table with no transformation SSIS 65% + faster than DTS Adding transformation would add more SSIS advantage Average Runtime

(seconds) DTS SSIS SQL Server Destination SSIS OLE DB

Destination 33.2 s 11.3 s 12.3 s Project Options

Run DTS in 2005 or 2008 Missing the package logs Runs under 32 bit Upgrade using MS Wizard Not compatible with most package Upgrade using DTS xChange Minutes per package

Starting from scratch About 3-6 hrs per package conservatively Running Packages Under 2005+ Need to download DTS Designer Components (part of SQL Server 2005 Feature Pack) Pro: Delays the upgrade hurdle

Con: Cannot create new packages (easily) Logging views historically in SQL Server 2000 not available Runs in 32 bit mode only (WOW) Support ceases in vNext demo

Running DTS Package in SQL Server 2005/2008 Microsoft Package Upgrade Wizard Built into SQL Server 2005/2008 Pros: Free Cons: Does not handle ODBC

Only handles a few types of text file use cases No Dynamic Properties Task No UDL or legacy database support in data pump Packages only have about a 20% chance of working demo Microsoft Package Upgrade Wizard Demo of MS Existing Wizard

Sample Upgrade Project Plan Scope and Number of Packages How long will it take you to migrate each type of task Use a tool to migrate Upgrade the ActiveX Script Task logic Test, test and test DTS xChange Feature Highlights Profiles DTS packages to help with a conversion project plan

Rapidly converts DTS Packages to SSIS (2005 or 2008) and applies SSIS best practices Converts tasks that are not handled by the existing SQL Server conversion wizard Includes a SSIS logging repository and reports for trending and alerting Includes BI xPress for new SSIS packages A Few of the Conversion Rules

Support for migration of children packages Logging to SQL Server, Text Files or via Event Handlers Consolidate Connection Managers Create configuration files automatically Create package transactions Checkpoints NULL handling Other Advantages

Handles text files properly Handles NULLs properly Handles ODBC for a source Migrates Dynamic Properties Tasks Advanced profiler to estimate your project Full validation of the output of the migration 94%+ package success rate in customer migrations over tens of thousands of packages

Error/Warning Trend Extract/Load Trend Runtime Trend demo Run Through Profiler Run Through Package Converter

Show SSIS Auditing Framework ActiveX Script Migration All tools mentioned migrate DTS ActiveX to ActiveX in SSIS ActiveX migrates to SSIS but you would not want to keep it there and it may not run Need for ActiveX Script Task has been replaced with built-in, easy to maintain SSIS tasks

File System Object = File System Task Mail objects = Send Mail Task (now has SMTP) ADO objects = Execute SQL Task demo DeActiveX Script Conversions BI xPress Product changing names from SSIS xPress

Helps audit, notify, manage and deploy packages Provides in code reusability in SSIS March release integrates with Business Intelligence Development Studio (BIDS) and Management Studio Get Notified On Failure Audit SSIS Packages

Deploy Packages Monitor SSIS Packages demo BI xPress Microsoft Program Partnership

Solution to remove DTS upgrade road block includes: DTS xChange BI xPress One year of support and maintenance SQL Server 2008 Training (performed online) Customer Testimonial Cigna Background:

Cigna wanted to upgrade to 64-bit SQL Server 2005 from 32-bit SQL 2000 to consolidate the number of servers. The challenge was migrating thousands of DTS packages on a timely basis. They purchased DTS xChange and have migrated half their environment in the past six months. Without DTS xChange Cigna estimates the upgrade would have taken years, the conversions would have been less precise and would have required significantly more testing resources. - Mark Weichman, DBA Manager

Reference: email: [email protected] question & answer Please download a demo at: [email protected]

Resources www.microsoft.com/teched www.microsoft.com/learning Sessions On-Demand & Community Microsoft Certification & Training Resources

http://microsoft.com/technet http://microsoft.com/msdn Resources for IT Professionals Resources for Developers www.microsoft.com/learning

Microsoft Certification and Training Resources Complete an evaluation on CommNet and enter to win! 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Recently Viewed Presentations

  • VSTO 2005 SE - download.microsoft.com

    VSTO 2005 SE - download.microsoft.com

    VSTO 2005. Vlastní ActionsPane - na úrovni dokumentů. Host Controls. Cached Data v dokumentech. Zpracování na straně serveru. Add-in pro Outlook 2003
  • Chapter 3 Paradigms and Communication Theory

    Chapter 3 Paradigms and Communication Theory

    Chapter 3 Paradigms and Communication Theory ... Social Constructivism Ethnography of Communication Structuration Theory Actor Network Theory Perspectives in QL Comm Critical Tradition Modernism/Postmodernism Feminism Postcolonial theory Critical race theory/Orientalism/Whiteness Cultural ...
  • 12 of Italy's Most Charming Small Towns

    12 of Italy's Most Charming Small Towns

    Atrani, Italy is a fishing village along the Amalfi coast on the Southwestern edge of Italy. This town has served artists as a source of inspiration including Dutch artist M.C. Escher, who completed a lithograph print in 1931 called Atrani,...
  • Tutorial 1 - Solano Community College

    Tutorial 1 - Solano Community College

    Using the Design Guides. Vertical ruler. Indicates measurements down the editing window. Horizontal ruler. Indicates measurements across the editing window. Grids. Arrangement of horizontal and vertical lines. Used to position and align objects in the editing window. New Perspectives on...
  • Seminar: Part I

    Seminar: Part I

    A Seminar on BRAND-BUILDING ADVERTISING Please use the CD in the FCB-Ulka Brand Building Advertising Cases book for the advertisements and TV commercials needed for the Seminar. P
  • STARTUP ESSENTIALS: Part 1 of 4 Starting Up Your Startup

    STARTUP ESSENTIALS: Part 1 of 4 Starting Up Your Startup

    Seed Financing Overview . Seed Stage of Development . The first stage of venture capital financing. Seed Stage financings are often comparatively modest amounts of capital provided to inventors or entrepreneurs to finance the early development of a new product...
  • Introduction to Sociology - robeson.k12.nc.us

    Introduction to Sociology - robeson.k12.nc.us

    Sociology as a Social Science. Sociology is one of 6 social sciences. A social science studies human behavior, institutions or functions of human society in a scientific manner.. Other social sciences: Anthropology. Psychology. Economics. Political science. History
  • Othello - Weebly

    Othello - Weebly

    Consider her agency (or lack thereof), cultural context, and double standards of the time that might inspire such a cynical attitude. Act V: The Fate of the fallen. Othello is unique for a Shakespearean tragedy in that its antagonist is...