Out with the old in with the new
1 / 27

Out With The Old In With the New - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Out With The Old In With the New' - rocio

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



  • 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


  • DTS Connections

  • Connection Managers

Ssis quick mapping


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

Running packages in 2005 2008


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


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

Gotchas to catch here
Gotchas to Catch Here

  • Providers

  • 3rd party tasks

  • Types of ActiveX scripting

Out with the old in with the new

  • 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

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


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


ActiveX Script Conversion

Loop conversion

The end so soon yawn
The End so Soon? (yawn)