Top 10 ssis best practices
Download
1 / 30

Top 10 SSIS Best Practices - PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Top 10 SSIS Best Practices' - duane


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


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


ad