Dts conversion to ssis
1 / 20

DTS Conversion to SSIS - PowerPoint PPT Presentation

  • Uploaded on

DTS Conversion to SSIS. Conversion Best Practices Mike Davis ( MDavis@pragmaticworks.com ). About the Speaker. BI consultant Specializing in SSIS, SSRS .Net Developer Four years experience with SQL Server Currently developing new products with Pragmatic Works for SQL Server

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 'DTS Conversion to SSIS' - tiana

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
Dts conversion to ssis

DTS Conversion to SSIS

Conversion Best Practices

Mike Davis


About the speaker
About the Speaker

  • BI consultant Specializing in SSIS, SSRS

  • .Net Developer

  • Four years experience with SQL Server

  • Currently developing new products with Pragmatic Works for SQL Server

  • Blog on Pragmaticworks.com

Why choose ssis
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

    Less Maintenance… Faster Development… Faster Performance

Dts vs ssis speed test
DTS vs. SSIS 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

Project options
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 DTSxchange

    • Minutes per package

  • Starting from scratch

    • About 3-5 hrs per package

Dts conversion to ssis

  • Running DTS package in SQL Server 2005/2008

Microsoft package upgrade wizard
Microsoft Package Upgrade Wizard

  • Built into SQL Server 2005/2008

    • 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% of working

What is involved in upgrading
What is Involved in Upgrading?

  • Scope and Number of Packages

  • How long will it take you to migrate each type of task

  • How to migrate, Manual or Tool

  • Upgrade the ActiveX Script Task logic, Manual

  • Test, test and test

Feature highlights
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

  • Handles Flat files Properly

  • Shows Warnings not just success

A few of the conversion rules
A Few of the Conversion Rules

  • Support for migration of children packages

  • Creates a robust Auditing framework

  • Consolidate Connections in Connection Manager

  • Create configuration files automatically

  • Create package transactions

  • Checkpoints

  • NULL handling

Other advantages
Other Advantages

  • Handles text files properly

  • Handles ODBC for a source

  • Migrates Dynamic Properties Tasks

  • Advanced profiler to estimate your project

  • Full validation of the output of the migration

  • 92% package success rate in customer migrations over thousands of packages

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


  • MS Wizard

  • Profiler

  • DTSxchange

  • ActiveX Script conversions

    For Questions Email :

  • MDavis@pragmaticworks.com

Next steps
Next Steps

  • Profiler is free at DTSxchange.com

  • Download a free trial of DTSxchange to convert 3 packages

  • Watch the demonstrations on DTSxchange.com


  • A copy of Professional SSIS will be given away.


  • Contact Pragmatic Sales Department

    • sales@pragmaticworks.com

    • MDavis@pragmaticworks.com (me)

  • Thank you for attending