chapter six

6 SSIS Development

 

This chapter covers

  • An introduction to SSIS and SSIS development mistakes
  • Losing bad data
  • Not optimizing data loads
  • Using SSIS as a T-SQL orchestration tool
  • Always extracting all the data from a source table
NOTE

Examples in this chapter will use SQL Server Data Tools (SSDT), which can be downloaded from https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver16#ssdt-for-visual-studio-2022 The Integration Services extension also needs to be installed, and is available in Extensions | Manage Extensions and then searching for SQL Server Integration Services Projects 2022 from the market place

SQL Server Integration Services, commonly known as SSIS is a tool which ships with SQL Server Enterprise and Standard editions, although there are feature limitations in standard edition. It is an Extract, Transform and Load (ETL) tool that allows developers to build data movement and transformation pipelines within a drag-and-drop GUI interface.

A SSIS package always consists of a single control flow, which orchestrates the tasks that the package will run. Within the control flow, we can create zero or more data flows, which are used to import, export, and transform data within memory buffers.

6.1 #22 Throwing Away Bad Data

6.2 #23 Not Optimizing Data Loads

6.3 #24 Using SSIS as a T-SQL Orchestration Tool

6.3.1 Create an Execute T-SQL Statement orchestration

6.3.2 Converting Execute T-SQL Statement tasks to data flows

6.4 #25 Extracting all data when we only need a subset

6.5 Summary