top 10 ssis best practices

Top 10 SSIS Best Practices

Tim MitchellArtis Consulting

The World’s Largest Communityof SQL Server Professionals

Session Objectives

• Review and discuss common best practices• Q&A

The World’s Largest Community of SQL Server Professionals

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

Housekeeping

• Questions• Surveys

The World’s Largest Community of SQL Server Professionals

Disclaimer

• My best practices != the only way

The World’s Largest Community of SQL Server Professionals

Best Practice #1: Configurations

• Externalize logic for packages• Easier administration, maintenance• Change once globally

The World’s Largest Community of SQL Server Professionals

Best Practice #1: Configurations

The World’s Largest Community of SQL Server Professionals

Best Practice #1: Configurations

The World’s Largest Community of SQL Server Professionals

Best Practice #2: Package Protection Level

• Never use EncryptSensitiveWithUserKey• When possible, use DontSaveSensitive– Previous tip

• If you must encrypt, use EncryptSensitiveWithPassword or EncryptAllWithPassword

The World’s Largest Community of SQL Server Professionals

Best Practice #2: Package Protection Level

The World’s Largest Community of SQL Server Professionals

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

Best Practice #3: Row Count Audit

The World’s Largest Community of SQL Server Professionals

Best Practice #3: Row Count Audit

The World’s Largest Community of SQL Server Professionals

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

Best Practice #4: Lookup Cache Modes

The World’s Largest Community of SQL Server Professionals

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

Best Practice #5: Event Handlers

The World’s Largest Community of SQL Server Professionals

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

Best Practice #6: Small SSIS Packages

The World’s Largest Community of SQL Server Professionals

Best Practice #7: Logging

• Log errors, warnings• Capture other standard events• Custom logging (supplement)

The World’s Largest Community of SQL Server Professionals

Best Practice #7: Logging

The World’s Largest Community of SQL Server Professionals

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

Best Practice #8: Script instead of complex expression

The World’s Largest Community of SQL Server Professionals

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

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

Best Practice #11: Annotations

• Document, document, document• Easy value add

The World’s Largest Community of SQL Server Professionals

Best Practice #12: Variables

• Naming convention• Package scope• Expressions

The World’s Largest Community of SQL Server Professionals

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

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

Thanks for Attending

Visit www.sqlservercentral.com for free SQL ServereBooks, articles, videos, blogs, news, and more.

Please Don’t Forget to Turn in Your Evaluations

The World’s Largest Communityof SQL Server Professionals