Top 10 ssis best practices
This presentation is the property of its rightful owner.
Sponsored Links
1 / 30

Top 10 SSIS Best Practices PowerPoint PPT Presentation


  • 54 Views
  • Uploaded on
  • Presentation posted in: General

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Top 10 ssis best practices

The World’s Largest Communityof SQL Server Professionals

Top 10 SSIS Best Practices

Tim Mitchell

ArtisConsulting


Session objectives

Session Objectives

Review and discuss common best practices

Q&A

The World’s Largest Community of SQL Server Professionals


Tim mitchell

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

Housekeeping

Questions

Surveys

The World’s Largest Community of SQL Server Professionals


Disclaimer

Disclaimer

My best practices != the only way

The World’s Largest Community of SQL Server Professionals


Best practice 1 configurations

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 configurations1

Best Practice #1: Configurations

The World’s Largest Community of SQL Server Professionals


Best practice 1 configurations2

Best Practice #1: Configurations

The World’s Largest Community of SQL Server Professionals


Best practice 2 package protection level

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


Best practice 2 package protection level1

Best Practice #2: Package Protection Level

The World’s Largest Community of SQL Server Professionals


Best practice 3 row count audit

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 audit1

Best Practice #3: Row Count Audit

The World’s Largest Community of SQL Server Professionals


Best practice 3 row count audit2

Best Practice #3: Row Count Audit

The World’s Largest Community of SQL Server Professionals


Best practice 4 lookup cache modes

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 modes1

Best Practice #4: Lookup Cache Modes

The World’s Largest Community of SQL Server Professionals


Best practice 5 event handlers

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 handlers1

Best Practice #5: Event Handlers

The World’s Largest Community of SQL Server Professionals


Best practice 6 small ssis packages

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 packages1

Best Practice #6: Small SSIS Packages

The World’s Largest Community of SQL Server Professionals


Best practice 7 logging

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 logging1

Best Practice #7: Logging

The World’s Largest Community of SQL Server Professionals


Best practice 8 script instead of complex expression

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 expression1

Best Practice #8: Script instead of complex expression

The World’s Largest Community of SQL Server Professionals


Best practice 9 naming convention

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

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

Best Practice #11: Annotations

  • Document, document, document

  • Easy value add

The World’s Largest Community of SQL Server Professionals


Best practice 12 variables

Best Practice #12: Variables

  • Naming convention

  • Package scope

  • Expressions

The World’s Largest Community of SQL Server Professionals


Best practice 13 etl frameworks

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

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


Top 10 ssis best practices

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


  • Login