Some time ago, I had the challenge of giving a 1-day introduction to SQL Server Integration Services. As you may expect, this was a difficult task because there is such much information related to SSIS that it was practically impossible to cover everything in only 1 day (9 hours to be precise). Knowing that the challenge was difficult to achieve, I took a different approach and decided to provide an overview of all the items, concepts, and tools that I believe needed to be understood and/or mastered in order to become a SSIS guru, the final outcome was a diagram that I called the “SSIS Technology Map” which is the subject of this post and I want to share with our fellas BI practitioners around the world.
The diagram has six major sections and each section has multiple subsections which are presented on a hierarchical way where colors and shapes are used to aid the visualization of the diagram. If you are someone like me who needs to visualize things and create mental maps in order to understand and learn, then you will hopefully find this diagram useful. Overall, this is just an attempt to show succinctly all the different concepts, and tools that need to be understood in order to become a SSIS guru. Note that sections and their content is expected to change and grow over time.
Without further ado, here’s the SSIS Technology Map:
Also, here’s a quick summary of each major section:
List of basic items, concepts, and tools associated to SSIS.
- Solution > Project > Package
- Connection Managers
- Control Flow
- Data Flow
- Configuration and Dynamism
- Event Handlers
List of items, concepts, and tools associated to the optimization of SSIS workflows.
- Buffer Size
- Scaling Out
- Data Flow Design Practices
- Performance Counters
List of items, concepts, and tools associated to the administration of SSIS.
- Basic Administrative Tasks
- SSIS Catalog
- Logging and Auditing
List of items, concepts, and tools associated to the use of SSIS as an ETL tool. Understanding of the concepts listed below is key to use SSIS successfully.
- Data Cleansing
- Data Profiling
- Fact Table Loading
- Dimension Table Loading
- Incremental Data Load
- Slow Changing Dimensions (SCD)
Debugging and Troubleshooting
List of items, concepts, and tools associated to debugging and troubleshooting SSIS.
- Performance Issues
- Package Failures
- Data Issues
List of some of the main components of the SSIS architecture.
- Run-time Engine
- Data-flow Engine.
Any questions or suggestions are more than welcome!5