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
SQL Server Integration Services, commonly known as SSIS, is a tool that ships with SQL Server Enterprise and Standard editions, although there are feature limitations in the 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.
NOTE
Examples in this chapter will use SQL Server Data Tools, which can be downloaded from https://mng.bz/RNB0. The Integration Services extension also needs to be installed and is available in Extensions | Manage Extensions and then by searching for SQL Server Integration Services Projects 2022 from the marketplace.
An 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.
Tasks on the control flow are joined by precedence constraints. This allows us to design our packages to run tasks serially, instead of all tasks running in parallel. Tasks following a precedence constraint are always run after the task(s) preceding the constraint.