Datasheet

Chapter 1: SSIS Solution Architecture
29
The chapters in this book flow in the natural progression that you may go through when designing your
SSIS solution. The next couple chapters provide you with the underlying SSIS support structure for your
solution — the storage, deployment, and management framework.
Next, as you delve into the data, you will be dealing with source data, whether in files or extracted from
a relational database management system (RDBMS) and often requiring a data - cleansing process.
Chapters 4 – 6 cover files, data extraction, and cleansing.
If your SSIS solution involves data warehouse ETL (which involves dimension and fact table loading,
and often cube processing) Chapters 7 – 9 are for you.
The final chapters address advanced package scalability and availability, advanced scripting for those
really complex scenarios, and performance tuning and design best practices.
Setting the Stage: Management and Deployment
One of the very first things you must design is an SSIS package template that integrates with a
management and auditing environment. You must do this upfront, because retrofitting your logging and
auditing while your packages are being designed is very difficult.
Chapter 2 examines building an SSIS management framework for this purpose. A management
framework is about knowing the what, when, and why of when a package executes. Knowing these
items is critical for SSIS administration and troubleshooting. Chapter 2 considers how to approach
creating package templates, supporting auditing structures, and centralized configurations.
The next design task is also related to management. It is defining your package deployment strategy and
your package storage model where you save your packages. Chapter 3 looks at all the surrounding
issues and factors involved in choosing the right model for your situation.
Deciding on a deployment and package storage model (the file system or in the
msdb system database) is
important early on because, as the number and design complexity of your SSIS packages grows (in an
organization), so do the challenges related to package deployment and management.
Source Data: Files, Tables, and Data Cleansing
Every ETL or data - integration project involves data. And this data must come from somewhere,
either from a file provided or a table (or query). Therefore, you are going to deal with source data in
some aspect.
Chapter 4 discusses how to deal with files. Many of the data - integration and processing projects involve
working with data files of many kinds, such as delimited files, binary files, image files, Excel files, or
XML files. These file types and other types may need to be moved around, modified, copied, or even
deleted, and their contents may need to be imported. Chapter 4 walks you through the methods and
practices using SSIS to handle file management.
Your project may not involve files, but even if it does, you will more than likely have to extract data from
a source database. Chapter 5 examines data extraction. SSIS solutions often have a data - extraction
component, which may involve connecting to relational systems like Oracle, DB2, Sybase, MySQL, or
TeraData. Chapter 5 considers the best practices for extracting data from various sources, and addresses
c01.indd 29c01.indd 29 9/24/09 11:26:44 AM9/24/09 11:26:44 AM