ssis coding conventions, best practices, tips and programming guidelines for sql server
TRANSCRIPT
SSISCoding Conventions, Best Practices, Tips and Programming Guidelines
BY-VISHAL PAWAR
Remember - SSIS is an in-memory pipeline
Extract data in parallelUse Specific SSIS Process
step loggingCalling a child package
multiple timesUnnecessary columns usage and avoidance
Pulling High Volumes of Data
Utilization of Checkpoint features
Remember - SSIS is case-sensitive for all name
Optimum use of event in event handlers
Utilize more SQL Script usage for optimization
SQL job with many atomic steps
Use queries instead of tables
Avoid SELECT * in SSIS Query
understanding SSIS resource utilization
Sequence containers to organize package
Configure Data access mode option in OLEDB
Email only when something is failing
Distributed transaction spanning multiple tasks
Filter, Sort, Group, Join in source
Effect of OLEDB Destination Settings
Application and Hardware contention
How much memory does my package use?
SSIS parallelism is Key for Optimization
Audit- and Event-Driven Logging
Limit the package names to 100 chars
Non-blocking, Semi-blocking & Full-blocking
Maximum Insert Commit Size Settings
Partial Caching and No Caching Usage
Use Proper data types in SSIS variable
Rows / sec = Row Count / TimeData Flow
Scheduling and monitoring SSIS Package
Excel Source and 64-bit runtime
High Volumes of Data and indexes
SQL Server Destination Adapter
Avoid using dot (.) is all names and script
Minimize logged operations
Don’t use too many single-threaded tasks
Keep it simple example getdate() from server
On failure component Decision stop/continue
formatting of the SSIS package must
Avoid asynchronous transformation
SQL Task is our best friend in SSIS
Get all configuration from SQL Table DB
Baseline source system extract speed
SSIS variable and Scoping Reach SSIS Use Protection
Level=DontSaveSensitiveBetter performance with
parallel executionAlways do performance
benchmark & Test
SSIS connection for Package use dynamic
SSIS and network topology relation
Sequencing, Formatting, & Annotation - Proper
filter your data in the Source Adapter
Lookup transformation consideration
DefaultBufferMaxSizeDefaultBufferMaxRows
Plan for capacity
precedence constraints to control package flow
SSIS and I/O bound Relationship
Grouping SSIS Task whenever possible
variables to store your expressions
implicit typecast Awareness
BufferTempStoragePathBLOBTempStoragePath
performance impact analysis for changes
Use caching in your LOOKUP components
Process / Private Bytes (DTEXEC.exe)
Improve drivers and driver configurations
Use common folder structure
use template packages to standardize
DelayValidation property True - skip
Priorities usage of custom and tasks
Rename all Name and Description properties
Process / Working Set (DTEXEC.exe)
Start multiple connections
Protection level -DontSaveSenstive
Develop a SSIS framework first
MaxConcurrentExecutables and Parallelism
Deployments – File System or SQL server
Sort in the query instead of in adaptors
Memory Manager/Total Server Memory-SQL
Use multiple NIC cards Copy pasting the Script component
Design Deployment & debugging strategy
Monitoring the SSIS Performance
Real-time SSIS Package calling and Needs
Extract required data with validation
SSIS - Memory / Page Reads / sec
Always log to a text file Optimal use of configuration records
Use BIDSHelperAdd in for productivity
Distributed Transaction in SSIS
Maintenance and Troubleshooting Impact
• BI Solution Architect • Blogging @ http://bimentalist.com , http://sqlmentalist.com• Knowledge sharing - 350+ BI Articles ,400 SQL Article , 350+ SQL Script • 4 Complex SQL tool innovator on Codeplex & open source all- https://goo.gl/OI3sB4• Slide share publication - http://www.slideshare.net/VishalPawar_BI• 2 LinkedIn Post - https://www.linkedin.com/today/author/120635304• Free Power BI Course publish on Udemy - https://goo.gl/ArL0qe , 1400 + Student • Leading Global Power BI User Group http://goo.gl/oV7IHz , 1200+ Member , 12 + Leaders • Professional Microsoft BI Trainer Mentalist Network • Founder & BI Consulting Partner of Right Click Info • NMUG Community Founder Leader , Speaker - http://nmug.org/• Microsoft Community Representative for Navi Mumbai region.• SQL PASS Chapter Lead for Mumbai & Navi Mumbai • MCTS: Microsoft® Certified Technology Specialist -SQL Server 2008 R2, BI • MCSA: Microsoft® Certified Solutions Associate• CSM: Certified Scrum Master from Scrum Alliance, US• SQL Server Expert in Architecting and Optimization • BE - Computer Science & Engineering , RAIT , Mumbai• MBA-Information System, Arianth College , Pune
About Me