200 likes | 285 Views
Meeting Date: September 27, 2007 Topic: Converting DTS to SSIS by Brian Knight. User Group Update. July & Aug Meetings Successful >50 registered attendees today >20 new registered members Upcoming meetings: Oct 16 – Rick Heiges – “The 64-bit Question.” Noted Speaker & PASS Board Member
E N D
Meeting Date: September 27, 2007 Topic: Converting DTS to SSIS by Brian Knight
User Group Update July & Aug Meetings Successful >50 registered attendees today >20 new registered members Upcoming meetings: Oct 16 – Rick Heiges – “The 64-bit Question.” Noted Speaker & PASS Board Member Nov – Andrew Kelly Performance expert & SQL Server MVP
Today’s Speaker Brian Knight Author & SQL Server MVP: Professional SQL Server 2005 Integration Services Expert SQL Server 2005 Integration Services Professional SQL Server 2000 DTS Programming and Extending SQL Server 2005 Integration Services Founder SQLServerCentral.com Founder Pragmatic Works Founder of JumpStartTV.com
Congratulations! • Welcome Camille Charene Knight. • Born ten days ago!
Migrating From DTS to SSIS Brian Knight, Pragmatic Works
Quick Intro • Brian Knight - Bknight@whiteknighttechnology.com • Founder of SQLServerCentral.com, JumpstartTV.com • SQL Server MVP • Owner of Pragmatic Works • Author of 7 SQL Server books
Agenda • Upgrading in place • The Good, the bad and the ugly of DTS migrations • Demo of a complete migration • DTS Package Migration Wizard • Other Options
Upgrading In Place • DTS runtime ships with SQL Server 2005 • Management Studio to view/execute packages • Can’t create packages though • Designer does not ship out of the box • Feature pack
The Good • Upgrade Adviser • Execute SQL tasks • Execute Process tasks • Execute Package tasks (with a caveat) • Bulk Insert Task
The Bad • ActiveX Scripts – 10% likeliness of working • Data Flow Tasks – 40% likeliness of working • Data Driven Query Tasks – 0% likeliness of working
The Ugly • SSAS tasks - 0% likeliness of working • Dynamic Properties tasks - 0% likeliness of working • Flat File Connections • Will have to open the Connection Manager in SSIS • Overall percentage of success apx. 40%
Are you Missing Out? • If conversion works, you probably miss out on the power of SSIS • Configurations • Logging • Checkpoints • Transactions • Much, much more.
Demo • SSIS foundation • Demo of a migration
Alternatives • Re-architect The Package From Scratch • New Migration Tool • Outsourcing
Outsourcing • Employ a Consulting Company • Formalized training around SSIS • Mentoring / Oversight • Review • Offshore/Onshore • Research • Guarantees and accountability • Testing and approval
DTS xChange • Allows you to migrate multiple servers and directories at once • Allows you to create sets of rules for the migration • Apply rules to the migration to use the new SSIS features and apply best practices • Releases in October • More info available on PragmaticWorks.com • bknight@pragmaticworks.com
xChange Migration Rules • Sample rules (more than 10 advanced rules): • Handle Dynamic Properties Task • Apply naming convention • Creating logging provider • Create event handlers for logging into enterprise DB • Make connections dynamic via expressions • Consolidate connections • Complex data flow • Add checkpoints • Add Transactions • Does not alleviate a human or testing!
Summary • Existing wizard approximately 40% effective • This is not as daunting as it looks • Education of your team • Migration through a tool like DTS xChange • Outsourcing
Questions • Open Q&A • bknight@whiteknighttechnology.com • Bill.Bickford@idea.com
CSSUG Follow Up Send me your feedback Thoughts on the virtual format Suggestions for meeting topics Suggestions for future speakers Check the web Site for meeting updates www.Charlotte-SQL.org Thanks