Book Image

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Book Image

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Overview of this book

SQL Server Integration Services (SSIS) is a leading tool in the data warehouse industry - used for performing extraction, transformation, and load operations. This book is aligned with the most common methodology associated with SSIS known as Extract Transform and Load (ETL); ETL is responsible for the extraction of data from several sources, their cleansing, customization, and loading into a central repository normally called Data Warehouse or Data Mart.Microsoft SQL Server 2012 Integration Services: An Expert Cookbook covers all the aspects of SSIS 2012 with lots of real-world scenarios to help readers understand usages of SSIS in every environment. Written by two SQL Server MVPs who have in-depth knowledge of SSIS having worked with it for many years.This book starts by creating simple data transfer packages with wizards and illustrates how to create more complex data transfer packages, troubleshoot packages, make robust SSIS packages, and how to boost the performance of data consolidation with SSIS. It then covers data flow transformations and advanced transformations for data cleansing, fuzzy and term extraction in detail. The book then dives deep into making a dynamic package with the help of expressions and variables, and performance tuning and consideration.

Related Content you might be interested in

Current Title:

Small book image
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
May, 2012 | 564 pages
No titles found
Table of Contents (23 chapters)
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Credits
Credits
Foreword
Foreword
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Getting Started with SQL Server Integration Services
Getting Started with SQL Server Integration Services
Introduction
Import and Export Wizard: First experience with SSIS
Getting started with SSDT
Creating the first SSIS Package
Getting familiar with Data Flow Task
SSIS 2012 versus previous versions in Developer Experience
2
Control Flow Tasks
Control Flow Tasks
Introduction
Executing T-SQL commands: Execute SQL Task
Handling file and folder operations: File System Task
Sending and receiving files through FTP: FTP Task
Executing other packages: Execute Package Task
Running external applications: Execute Process Task
Reading data from web methods: Web Service Task
Transforming, validating, and querying XML: XML Task
Profiling table statistics: Data Profiling Task
Batch insertion of data into a database: Bulk Insert Task
Querying system information: WMI Data Reader Task
Querying system events: WMI Event Watcher Task
Transferring SQL server objects: DBMS Tasks
3
Data Flow Task Part 1—Extract and Load
Data Flow Task Part 1—Extract and Load
Introduction
Working with database connections in Data Flow
Working with flat files in Data Flow
Passing data between packages—Raw Source and Destination
Importing XML data with XML Source
Loading data into memory—Recordset Destination
Extracting and loading Excel data
Change Data Capture
4
Data Flow Task Part 2—Transformations
Data Flow Task Part 2—Transformations
Introduction
Derived Column: adding calculated columns
Audit Transformation: logging in Data Flow
Aggregate Transform: aggregating the data stream
Conditional Split: dividing the data stream based on conditions
Lookup Transform: performing the Upsert scenario
There's more...
OLE DB Command: executing SQL statements on each row in the data stream
Merge and Union All transformations: combining input data rows
Merge Join Transform: performing different types of joins in data flow
Multicast: creating copies of the data stream
Working with BLOB fields: Export Column and Import Column transformations
Slowly Changing Dimensions (SCDs) in SSIS
5
Data Flow Task Part 3—Advanced Transformation
Data Flow Task Part 3—Advanced Transformation
Introduction
Pivot and Unpivot Transformations
Text Analysis with Term Lookup and Term Extraction transformations
DQS Cleansing Transformation—Cleansing Data
Fuzzy Transformations—how SSIS understands fuzzy similarities
6
Variables, Expressions, and Dynamism in SSIS
Variables, Expressions, and Dynamism in SSIS
Introduction
Variables and data types
Using expressions in Control Flow
Using expressions in Data Flow
The Expression Task
Dynamic connection managers
Dynamic data transfer with different data structures
7
Containers and Precedence Constraints
Containers and Precedence Constraints
Introduction
Sequence Container: putting all tasks in an executable object
For Loop Container: looping through static enumerator till a condition is met
Foreach Loop Container: looping through result set of a database query
Foreach Loop Container: looping through files using File Enumerator
Foreach Loop Container: looping through data table
Precedence Constraints: how to control the flow of task execution
8
Scripting
Scripting
Introduction
The Script Task: Scripting through Control Flow
The Script Component as a Transformation
The Script Component as a Source
The Script Component as a Destination
The Asynchronous Script Component
9
Deployment
Deployment
Introduction
Project Deployment Model: Project Deployment from SSDT
Using Integration Services Deployment Wizard and command-line utility for deployment
The Package Deployment Model, Using SSDT to deploy package
Creating and running Deployment Utility
DTUTIL—the command-line utility for deployment
Protection level: Securing sensitive data
10
Debugging, Troubleshooting, and Migrating Packages to 2012
Debugging, Troubleshooting, and Migrating Packages to 2012
Introduction
Troubleshooting with Progress and Execution Results tab
Breakpoints, Debugging the Control Flow
Handling errors in Data Flow
Migrating packages to 2012
Data Tap
11
Event Handling and Logging
Event Handling and Logging
Introduction
Logging over Legacy Deployment Model
Logging over Project Deployment Model
Using event handlers and system variables for custom logging
12
Execution
Execution
Introduction
Execution from SSMS
Execution from a command-line utility
Execution from a scheduled SQL Server Agent job
13
Restartability and Robustness
Restartability and Robustness
Introduction
Parameters: Passing values to packages from outside
Package configuration: Legacy method to inter-relation
Transactions: Doing multiple operations atomic
Checkpoints: The power of restartability
SSIS reports and catalog views
14
Programming SSIS
Programming SSIS
Introduction
Creating and configuring Control Flow Tasks programmatically
Working with Data Flow components programmatically
Executing and managing packages programmatically
Creating and using Custom Tasks
15
Performance Boost in SSIS
Performance Boost in SSIS
Introduction
Control Flow Task and variables considerations for boosting performance
Data Flow best practices in Extract and Load
Data Flow best practices in Transformations
Working with buffer size
Working with performance counters
Index
Index

Aggregate Transform: aggregating the data stream

Aggregate Transform provides a way to group data by column(s) and apply aggregation functions on them.

In this recipe we will fetch each Sales Person according to the start and end date of sales and the sum of their sales quota by resorting to an Aggregate Transform.

How to do it...

  1. Create a new Integration Services project and name it R03_Aggregate.

  2. Add a Data Flow Task and in the Data Flow tab add an OLE DB Source and then create a connection to the AdventureWorks2012 sample database. Set the data access mode to SQL Command and enter the following T-SQL code in the SQL Command field:

    SELECT SLHST.QuotaDate, SLHST.SalesQuota, 
  PER.FirstName, PER.LastName 
FROM Sales.SalesPersonQuotaHistory SLHST 
 INNER JOIN Person.Person PER 
 ON SLHST.BusinessEntityID = PER.BusinessEntityID

  3. Click on Preview and check the structure of your data.

  4. Drag-and-drop an Aggregate Transform right after the OLE DB Source and connect the Data Path to the aggregate transformation...

Previous Section
End of Section 5
Next Section