Tuesday, October 19, 2010

Tips for ETL Design

First of all we should know what exactly is the requirement?
We must have the clear knowledge of Blocking, Semi-blocking and non-blocking transformations.
Try to use the best options rather than going with any of the available options in SSIS tools.

Best Practices
#1 - Pulling High Volumes of Data
#2 - Avoid SELECT *
#3 - Effect of OLEDB Destination Settings
#4 - Effect of Rows Per Batch and Maximum Insert Commit Size Settings
#5 - SQL Server Destination Adapter
#6 - Avoid asynchronous transformation (such as Sort Transformation) wherever possible
#7 - DefaultBufferMaxSize and DefaultBufferMaxRows
#8 - BufferTempStoragePath and BLOBTempStoragePath
#9 - How DelayValidation property can help you
#10 - Better performance with parallel execution
#11 - When to use events logging and when to avoid
#12 - Monitoring the SSIS Performance with Performance Counters
#13 - Distributed Transaction in SSIS and its impact
#14 - How Checkpoint features helps in package restarting