Out with the old in with the new
Sponsored Links
This presentation is the property of its rightful owner.
1 / 27

Out With The Old In With the New PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on
  • Presentation posted in: General

Out With The Old In With the New. Conversion Best Practices Devin Knight Senior BI Consultant ([email protected] ). Agenda. Mapping DTS knowledge and skills to SSIS Running DTS packages in 2005 or 2008 Upgrading DTS Packages Upgrading ActiveX Scripts. Why choose SSIS?.

Download Presentation

Out With The Old In With the New

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Out With The Old In With the New

Conversion Best Practices

Devin Knight

Senior BI Consultant

([email protected])


Agenda

  • Mapping DTS knowledge and skills to SSIS

  • Running DTS packages in 2005 or 2008

  • Upgrading DTS Packages

  • Upgrading ActiveX Scripts


Why choose SSIS?

  • 64bit support

  • Faster architecture

  • Logging, Configuration, CheckPoint etc.

  • Source control

  • More stuff


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


Designing Packages

  • DTS Designer

  • Business Intelligence Development Studio (BIDS)


Comparing DTS to SSIS Objects

  • Transform Data Task

  • Data Flow Task


Connections

  • DTS Connections

  • Connection Managers


Demo

SSIS Quick Mapping


Project Options

  • Run DTS in 2005 or 2008

    • Missing the package logs

    • Runs under 32 bit

  • Upgrade using MS Wizard

    • Processes a limited set of simple packages

  • 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


Common Errors

  • Need to install the 2005 Backwards Compatibility Kit

  • 2000 DTS Designer Components

  • In 2008, copy two additional DLL files over and RLL files


64 Bit Gotcha


Demo

Running Packages in 2005/2008

Running in legacy mode


Microsoft Package Upgrade Wizard

  • Built into SQL Server 2005/2008

  • Pros:

    • Free

    • Works on simple packages

  • 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

DTS Migration Wizard

Conversion using 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


Profiling


Gotchas to Catch Here

  • Providers

  • 3rd party tasks

  • Types of ActiveX scripting


Demo

  • Free DTS Profiler tool.


DTS xChange Feature Highlights

  • Profiles DTS packages to help with a conversion project plan

  • Coverts to 2005 or 2008 (R2)

  • Logging and auditing framework

  • Includes BI xPress for new SSIS packages

  • Have converted more than 300,000+ packages


Why is it Better?


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


Runtime Trend

Error/Warning Trend

Extract/Load Trend


Demo

DTS xChange

Profile, Convert and Monitor


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

ActiveX Script Conversion

Loop conversion


The End so Soon? (yawn)

http://www.bidn.com/blogs/devinknight

@knight_devin

[email protected]


  • Login