The SSIS Technology Map

By Tuesday, April 28, 2015 Permalink 5

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:

Download the PDF file .

Also, here’s a quick summary of each major section:

Main Tools

List of basic items, concepts, and tools associated to SSIS.

  • Solution > Project > Package
  • Connection Managers
  • Control Flow
  • Data Flow
  • Configuration and Dynamism
  • Deployment
  • Execution
  • Wizards
  • Programming
  • Event Handlers

Optimization

List of items, concepts, and tools associated to the optimization of SSIS workflows.

  • Buffer Size
  • Scaling Out
  • Data Flow Design Practices
  • Performance Counters

Administration

List of items, concepts, and tools associated to the administration of SSIS.

  • Basic Administrative Tasks
  • SSIS Catalog
  • Security
  • Logging and Auditing

Data Integration

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

Architecture

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

No Comments Yet.

Leave a Reply

Your email address will not be published. Required fields are marked *