1 / 20

Meeting Date: September 27, 2007 Topic: Converting DTS to SSIS by Brian Knight

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

zoe-bowen
Download Presentation

Meeting Date: September 27, 2007 Topic: Converting DTS to SSIS by Brian Knight

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Meeting Date: September 27, 2007 Topic: Converting DTS to SSIS by Brian Knight

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

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

  4. Congratulations! • Welcome Camille Charene Knight. • Born ten days ago!

  5. Migrating From DTS to SSIS Brian Knight, Pragmatic Works

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

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

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

  9. The Good • Upgrade Adviser • Execute SQL tasks • Execute Process tasks • Execute Package tasks (with a caveat) • Bulk Insert Task

  10. The Bad • ActiveX Scripts – 10% likeliness of working • Data Flow Tasks – 40% likeliness of working • Data Driven Query Tasks – 0% likeliness of working

  11. 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%

  12. Are you Missing Out? • If conversion works, you probably miss out on the power of SSIS • Configurations • Logging • Checkpoints • Transactions • Much, much more.

  13. Demo • SSIS foundation • Demo of a migration

  14. Alternatives • Re-architect The Package From Scratch • New Migration Tool • Outsourcing

  15. Outsourcing • Employ a Consulting Company • Formalized training around SSIS • Mentoring / Oversight • Review • Offshore/Onshore • Research • Guarantees and accountability • Testing and approval

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

  17. 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!

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

  19. Questions • Open Q&A • bknight@whiteknighttechnology.com • Bill.Bickford@idea.com

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

More Related