SQL Server Administration and Maintenance

SQT05 Loading a Data Warehouse Using SSIS


1:30pm - 2:45pm

Level: Introductory to Intermediate

Tim Mitchell

Data Platform MVP


Tyleris Data Solutions

Data warehouses and ETL processes are an essential part of business decision-making. In an age where big data and NoSQL solutions, traditional data warehouse architectures and ETL tools to feed them (such as SQL Server Integration Services) remain critical to providing reliable, repeatable, and auditable answers.

SQL Server Integration Services has evolved into an ideal tool for loading data warehouses. Its modular design and variety of load tools allow it to process most any data warehouse load design (full load, incremental load, or near real-time), while its data flow transformations and scripting tools make it flexible enough to handle complex reshaping and cleansing required of data warehouse loads.

In this session, we will discuss and demonstrate loading a data warehouse using SQL Server Integration Services. We will briefly cover some ETL best practices for data warehouse loads, and will demonstrate how these practices can be implemented using SSIS. Among the topics we'll cover:

  • Why SSIS for data warehouse loads?
  • Handling different DW load types (full, incremental) in SSIS
  • SSIS Design patterns for loading facts and dimensions
  • Change detection in SSIS
  • Data lineage