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

  • Uploaded on
  • Presentation posted in: General

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?.

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




  • 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

64 bit gotcha

64 Bit Gotcha

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

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


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)




  • Login