1 / 30

Top 10 SSIS Best Practices

The World’s Largest Community of SQL Server Professionals. Top 10 SSIS Best Practices. Tim Mitchell Artis Consulting. Session Objectives. Review and discuss common best practices Q&A. Tim Mitchell. Business Intelligence Consultant – Artis Consulting, Dallas TX

duane
Download Presentation

Top 10 SSIS Best Practices

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. The World’s Largest Communityof SQL Server Professionals Top 10 SSIS Best Practices Tim Mitchell ArtisConsulting

  2. Session Objectives Review and discuss common best practices Q&A The World’s Largest Community of SQL Server Professionals

  3. Tim Mitchell Business Intelligence Consultant – Artis Consulting, Dallas TX Contributing author, MVP Deep Dives 2 Coauthor, SSIS Design Patterns North Texas SQL Server User Group SQL Server MVP TimMitchell.net | Twitter.com/Tim_Mitchell The World’s Largest Community of SQL Server Professionals

  4. Housekeeping Questions Surveys The World’s Largest Community of SQL Server Professionals

  5. Disclaimer My best practices != the only way The World’s Largest Community of SQL Server Professionals

  6. Best Practice #1: Configurations Externalize logic for packages Easier administration, maintenance Change once globally The World’s Largest Community of SQL Server Professionals

  7. Best Practice #1: Configurations The World’s Largest Community of SQL Server Professionals

  8. Best Practice #1: Configurations The World’s Largest Community of SQL Server Professionals

  9. Best Practice #2: Package Protection Level • Never use EncryptSensitiveWithUserKey • When possible, use DontSaveSensitive • Previous tip • If you must encrypt, use EncryptSensitiveWithPasswordor EncryptAllWithPassword The World’s Largest Community of SQL Server Professionals

  10. Best Practice #2: Package Protection Level The World’s Largest Community of SQL Server Professionals

  11. Best Practice #3: Row Count Audit • In/out rowcount for proper package audit • Insure precise control over data pipeline • May help satisfy regulatory requirements The World’s Largest Community of SQL Server Professionals

  12. Best Practice #3: Row Count Audit The World’s Largest Community of SQL Server Professionals

  13. Best Practice #3: Row Count Audit The World’s Largest Community of SQL Server Professionals

  14. Best Practice #4: Lookup Cache Modes • Default = Full Cache • Full Cache is case sensitive, other modes depend on database collation • UPPER() and LOWER() • Partial cache • No cache – avoid if possible The World’s Largest Community of SQL Server Professionals

  15. Best Practice #4: Lookup Cache Modes The World’s Largest Community of SQL Server Professionals

  16. Best Practice #5: Event Handlers • Handle various events, errors and otherwise • Address errors inline • Respond to other events The World’s Largest Community of SQL Server Professionals

  17. Best Practice #5: Event Handlers The World’s Largest Community of SQL Server Professionals

  18. Best Practice #6: Small SSIS Packages • One package = one logical unit of work • Load a fact table • Load a single dimension • Easier to debug • Faster validation • Reusability The World’s Largest Community of SQL Server Professionals

  19. Best Practice #6: Small SSIS Packages The World’s Largest Community of SQL Server Professionals

  20. Best Practice #7: Logging • Log errors, warnings • Capture other standard events • Custom logging (supplement) The World’s Largest Community of SQL Server Professionals

  21. Best Practice #7: Logging The World’s Largest Community of SQL Server Professionals

  22. Best Practice #8: Script instead of complex expression • Replace complex expressions with script task or script component • Easier to develop • Inline comments • Intellisense • Error handling The World’s Largest Community of SQL Server Professionals

  23. Best Practice #8: Script instead of complex expression The World’s Largest Community of SQL Server Professionals

  24. Best Practice #9: Naming Convention • Use descriptive names for containers, tasks, components • Use a standard naming convention for SSIS objects • No functional difference, but helps to quickly identify type • SSIS API The World’s Largest Community of SQL Server Professionals

  25. Best Practice #10: MERGE • T-SQL MERGE statement usually outperforms SSIS SCD components (native or 3rd party) • Some limits on use • Staging The World’s Largest Community of SQL Server Professionals

  26. Best Practice #11: Annotations • Document, document, document • Easy value add The World’s Largest Community of SQL Server Professionals

  27. Best Practice #12: Variables • Naming convention • Package scope • Expressions The World’s Largest Community of SQL Server Professionals

  28. Best Practice #13: ETL Frameworks • Managed execution of packages • Group packages according to function, dependency, and precedence • Unified logging and error handling • Free and commercial tools The World’s Largest Community of SQL Server Professionals

  29. Best Practice #14: Custom Components • Repetitive use of the same logic in multiple packages • Custom component allows easy deployment The World’s Largest Community of SQL Server Professionals

  30. The World’s Largest Communityof SQL Server Professionals Thanks for AttendingVisit www.sqlservercentral.com for free SQL ServereBooks, articles, videos, blogs, news, and more. Please Don’t Forget to Turn in Your Evaluations

More Related