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.

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.

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.