Search

SQL Server Parallel Data Warehouse (PDW) is Microsoft’s massively parallel processing (MPP) offering and is available as part of Microsoft’s Analytics Platform System (APS). APS is a turnkey solution focused on big data analytics. It offers two regions, or software options, to customers: PDW and HDInsight, which is Microsoft’s 100% Apache Hadoop distribution. PDW is built upon the SQL Server platform, although it is a separate product with a build of SQL Server specifically designed to support MPP operations.

Elegant solutions lead to more elegant solutions. This is one feature of elegant solutions, and Business Intelligence Markup Language (Biml) is an elegant solution. Biml facilitates the rapid development of SSIS packages. As impressive as this is, Biml can do more – much more. We will not be able to explore Biml’s complete feature set in this volume, but I would like to examine some additional functionality.

Processing Slowly Changing Dimensions (SCDs) is a common ETL operation when dealing with Data Warehouses. The SSIS Data Flow has a Slowly Changing Dimension Transform, which provides a wizard that outputs a set of transforms needed to handle the multiple steps of processing SCDs. While the built-in SCD Transform can be useful, it is not ideal for all data loading scenarios. This chapter describes how to make the most of the SCD Transform, and provides a couple of alternative patterns you can use.

In the previous chapters in this section, you learned about patterns that relate to SQL Server and Oracle Sources. In this chapter, I will move on to patterns that relate to sourcing data from the IBM DB2 database. DB2 describes a variety of databases, so it is essential to learn about the different databases I will discuss, as well as how to use each database as an Integration Services source.

A common way to transfer data between systems is to export the source data to a flat file and then import the contents of this file into the destination database. Flat files come in all shapes, sizes, and types. There are no rowlength limitations. File size is limited by the maximum size allowed by the operating system. When examining flat file types, there are two initial considerations: file format and schema. Common file formats of flat file sources include: Comma-Separated Values (CSV) Tab-Delimited File (TDF) Fixed Width

Data Quality Services (DQS) was first introduced in SQL Server 2012. It provides data correction and data deduplication functionality—key components for most Extract, Transform, and Load (ETL) processes. This chapter describes how DQS integrates with SSIS and provides patterns that enable you to achieve reliable, low-effort data cleansing within your ETL packages.

SSIS frameworks are the next logical step after SSIS design patterns because frameworks comprise many patterns. At a minimum, an SSIS framework should provide package execution control and monitoring. That sounds simple, but I assure you, it is not. Execution control requires a working knowledge of the Integration Services runtime API. Understanding the subtleties of tight—and loose—coupling is not merely helpful, it can make or ruin your day (or data integration solution).

Data Quality Services (DQS) is a new product in SQL Server 2012 that provides data cleansing functionality — a key component for most ETL processes. This chapter describes how DQS integrates with SSIS, and provides patterns that enable you to achieve reliable, low effort data cleansing within your ETL packages.

Out of the various duties assigned to the ETL project manager or architect, one of the most critical is the establishment of a project estimate. When considering a new project of any type, executives, managers, and other decision makers will ask first, “How long will it take?” Appropriately setting time frame expectations is critical to the success of the project.

In earlier versions of Integration Services, the data movement platform did not include a management framework, which is the implementation of the execution, logging, and deployment of the Integration Services packages. To try to fill this hole, developers created their own management framework to use in their organizations. As with any custom solution, the developers found that the management framework needed to be cared for and upgraded when new versions or new packages were introduced into the system.

In the first section of this book, we looked at patterns focused on the control flow area of SQL Server Integration Services, including metadata, workflow execution, and scripting. The second section focuses on the data flow area of SQL Server Integration Services. This and the following chapters will discuss source, transformation, and logging patterns in the pipeline area of an Integration Services package.

You likely purchased this book to learn how to be a more productive SQL Server Integration Services (SSIS) developer. I applaud your desire and decision, and I sincerely hope the information contained herein has provided ideas and information to help you be more productive. I am always on the lookout for ways to become a better data integration developer. Specifically, I seek out ways to improve code quality and reduce the amount of time required to build solutions. Those goals are what motivated me to begin practicing patterns-based development in the first place, which eventually led to the idea for this book.

Microsoft’s SQL Server Parallel Data Warehouse (PDW) appliance was introduced with SQL Server 2008 R2 and is Microsoft’s first massively parallel processing (MPP) offering. Although PDW is built upon the SQL Server platform, it is a completely different product. As the name suggests, MPP uses multiple servers working as one system, called an appliance, to achieve much greater performance and scan rates than in traditional SMP systems. SMP refers to symmetric multi-processing; most database systems, such as all other versions of SQL Server, are SMP.

The expression language in SSIS might appropriately be referred to as the glue that holds the product together. Expressions in SSIS provide a relatively simple and easy-to-use interface to allow data developers to introduce dynamic logic into the ETL infrastructure. When you think through the various moving parts within Integration Services, you’ll likely discover that you can manipulate all of them in one way or another by using expressions.