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

Out With The Old In With the New PowerPoint PPT Presentation


  • 55 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

Out With The Old In With the New

Conversion Best Practices

Devin Knight

Senior BI Consultant

([email protected])


Agenda

Agenda

  • Mapping DTS knowledge and skills to SSIS

  • Running DTS packages in 2005 or 2008

  • Upgrading DTS Packages

  • Upgrading ActiveX Scripts


Why choose ssis

Why choose SSIS?

  • 64bit support

  • Faster architecture

  • Logging, Configuration, CheckPoint etc.

  • Source control

  • More stuff


Dts vs ssis engine speed test

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

Designing Packages

  • DTS Designer

  • Business Intelligence Development Studio (BIDS)


Comparing dts to ssis objects

Comparing DTS to SSIS Objects

  • Transform Data Task

  • Data Flow Task


Connections

Connections

  • DTS Connections

  • Connection Managers


Ssis quick mapping

Demo

SSIS Quick Mapping


Project options

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

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

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

64 Bit Gotcha


Running packages in 2005 2008

Demo

Running Packages in 2005/2008

Running in legacy mode


Microsoft package upgrade wizard

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


Dts migration wizard

Demo

DTS Migration Wizard

Conversion using existing wizard


Sample upgrade project plan

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

Profiling


Gotchas to catch here

Gotchas to Catch Here

  • Providers

  • 3rd party tasks

  • Types of ActiveX scripting


Out with the old in with the new

Demo

  • Free DTS Profiler tool.


Dts xchange feature highlights

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

Why is it Better?


Other advantages

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


Out with the old in with the new

Runtime Trend

Error/Warning Trend

Extract/Load Trend


Dts xchange

Demo

DTS xChange

Profile, Convert and Monitor


Activex script migration

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


Activex script conversion

Demo

ActiveX Script Conversion

Loop conversion


The end so soon yawn

The End so Soon? (yawn)

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

@knight_devin

[email protected]


  • Login