Upgrading DTS Packages to Microsoft SQL Server Integration
- Slides: 36
Upgrading DTS Packages to Microsoft SQL Server Integration Services Wouter Smit
About the Speaker Wouter has been working in the data warehousing field for more than 10 years MCITP Professional Database Administrator Database Developer Business Intelligence Developer Worked on a multitude of database platforms Working for a company called 3 fifteen technology solutions
About this session A presentation that was done at the North American Tech-Ed Done by Brian Knight SQL Server MVP, and co-founder of SQLServer. Central. com and Jumpstart. TV. com www. Pragmatic. Works. com Written SQL Server books
Agenda Mapping DTS Knowledge and Skills to SSIS Running DTS Packages in SQL 2005 or 2008 Upgrading DTS Packages Upgrading Active. X Scripts
Why Choose SSIS? 64 bit support Parallel in-memory multi buffer architecture helps to load data several times faster than DTS Logging, configuration, Check. Point 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 Active. X
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 Package needs to run from the destination server Average Runtime (seconds) DTS SSIS SQL Server Destination SSIS OLE DB Destination 33. 2 s 11. 3 s 12. 3 s
Designing Packages DTS Designer
Designing Packages Business Intelligence Development Studio (BIDS)
Comparing DTS to SSIS Objects Transform Data Task
Comparing DTS to SSIS Objects 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 Not compatible with most package Upgrade using DTS x. Change 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 v. Next
Common Errors Need to Install the 2005 Backward Compatibility Kit 2005 DTS Designer Components In SQL 2008, copy two additional DLL files over and RLL files
demo Running DTS Package in SQL Server 2005/2008
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 Microsoft Package Upgrade Wizard Demo of MS 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 Active. X Script Task logic Test, test and test
DTS x. Change 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 Includes a SSIS logging repository and reports for trending and alerting Includes BI x. Press for new SSIS packages
Monitor Packages
demo DTS x. Change Profile, Convert and Monitor
A Few of the Conversion Rules Support for migration of children packages Logging to SQL Server, Text Files or via Event Handlers Consolidate Connection Managers Create configuration files automatically Create package transactions Checkpoints NULL handling
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
Monitor Packages Error/Warning Trend Extract/Load Trend Runtime Trend
demo Run Through Profiler Run Through Package Converter Show SSIS Auditing Framework
Active. X Script Migration All tools mentioned migrate DTS Active. X to Active. X in SSIS Active. X migrates to SSIS but you would not want to keep it there and it may not run Need for Active. X 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 De. Active. X Script Conversions
BI x. Press Product changing names from SSIS x. Press Helps audit, notify, manage and deploy packages Provides in code reusability in SSIS Integrates with Business Intelligence Development Studio (BIDS) and Management Studio
BI x. Press
demo BI x. Press
question & answer Please download a demo at: bknight@pragmaticworks. com
Resources www. microsoft. com/teched www. microsoft. com/learning Sessions On-Demand & Community Microsoft Certification & Training Resources http: //microsoft. com/technet http: //microsoft. com/msdn Resources for IT Professionals Resources for Developers www. microsoft. com/learning Microsoft Certification and Training Resources
Complete an evaluation on Comm. Net and enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
- Sql server 2000 dts designer components
- Rhel 6 to 7 upgrade
- Resid upgrading
- Microsoft sql server 2005 analysis services
- Microsoft sql server machine learning
- Microsoft sql server introduction
- Microsoft sql server security best practices
- Sql server 2019 polybase
- Microsoft sql server management studio express 2005
- Microsoft sql server 2012 parallel data warehouse
- Microsoft sql server migration assistant for oracle
- Kereberos
- Azure sql edge raspberry pi
- Sql security best practices
- Sql compact
- Microsoft identity manager tutorial
- Microsoft host integration server
- Microsoft dts
- Difference between oracle and pl sql
- Pl sql unit test
- Azure sql server advanced data security
- Sql server virtualization
- Sql server security basics
- Sql server 101
- Query optimizer sql server
- How to monitor log shipping in sql server 2005
- Wait stats in sql server
- Sql server 2016 sp
- Sql server always on architecture diagram
- Sql server columnstore index best practices
- Pal sql server
- Sql server 組態管理員
- Sql 2005 performance
- Sql master data management
- Lck_m_is wait type in sql server
- Mpdwsvc.exe
- Sql server unstructured data