Librería Portfolio

Fill the gap between planning and doing with SSIS 2014
The 2014 release of Microsoft´s SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage.

Implement new best practices for effective use of SSIS
Work through tutorials for hands-on learning of complex techniques
Read case studies that illustrate the more advanced concepts
Learn directly from the foremost authority on SSIS
SQL Server Integration Services is a complex tool, but it´s the lifeblood of your work. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. You need to make sure the right architecture is in place. Professional Microsoft SQL Server 2014 Integration Services is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success.


Table of Contents
Introduction xxvii
Chapter 1: Welcome to SQL Server Integration Services 1

SQL Server SSIS Historical Overview 2

What's New in SSIS 2

Tools of the Trade 3

Import and Export Wizard 3

The SQL Server Data Tools Experience 4

SSIS Architecture 5

Packages 5

Control Flow 5

Data Flow 9

Variables 14

Parameters 14

Error Handling and Logging 14

Editions of SQL Server 14

Summary 15

Chapter 2: The SSIS Tools 17

Import and Export Wizard 17

SQL Server Data Tools 24

The Solution Explorer Window 26

The SSIS Toolbox 27

The Properties Windows 28

The SSIS Package Designer 28

Control Flow 29

Connection Managers 32

Variables 33

Data Flow 34

Parameters 35

Event Handlers 35

Package Explorer 36

Executing a Package 37

Management Studio 37

Summary 37

Chapter 3: SSIS Tasks 39

SSIS Task Objects 40

Using the Task Editor 40

The Task Editor Expressions Tab 41

Looping and Sequence Tasks 41

Script Task (.NET) 41

Analysis Services Tasks 44

Analysis Services Execute DDL Task 44

Analysis Services Processing Task 44

Data Mining Query Task 46

Data Flow Task 47

Data Preparation Tasks 48

Data Profiler 48

File System Task 50

Archiving a File 52

FTP Task 53

Getting a File Using FTP 54

Web Service Task 56

Retrieving Data Using the Web Service Task and XML Source Component 59

XML Task 62

Validating an XML File 64

RDBMS Server Tasks 66

Bulk Insert Task 66

Using the Bulk Insert Task 69

Execute SQL Task 71

Workflow Tasks 82

Execute Package Task 82

Execute Process Task 84

Message Queue Task 86

Send Mail Task 87

WMI Data Reader Task 88

WMI Event Watcher Task 91

Polling a Directory for the Delivery of a File 91

SMO Administration Tasks 92

Transfer Database Task 93

Transfer Error Messages Task 94

Transfer Logins Task 94

Transfer Master Stored Procedures Task 95

Transfer Jobs Task 96

Transfer SQL Server Objects Task 96

Summary 97

Chapter 4: The Data Flow 99

Understanding the Data Flow 99

Data Viewers 100

Sources 101

OLE DB Source 102

Excel Source 104

Flat File Source 105

Raw File Source 110

XML Source 110

ADO.NET Source 111

Destinations 111

Excel Destination 112

Flat File Destination 112

OLE DB Destination 112

Raw File Destination 113

Recordset Destination 114

Data Mining Model Training 114

DataReader Destination 114

Dimension and Partition Processing 114

Common Transformations 115

Synchronous versus Asynchronous Transformations 115

Aggregate 115

Conditional Split 117

Data Conversion 118

Derived Column 119

Lookup 121

Row Count 121

Script Component 122

Slowly Changing Dimension 123

Sort 123

Union All 125

Other Transformations 126

Audit 126

Character Map 128

Copy Column 128

Data Mining Query 129

DQS Cleansing 130

Export Column 130

Fuzzy Lookup 132

Fuzzy Grouping 139

Import Column 143

Merge 145

Merge Join 146

Multicast 146

OLE DB Command 147

Percentage and Row Sampling 148

Pivot Transform 149

Unpivot 152

Term Extraction 154

Term Lookup 158

Data Flow Example 160

Summary 164

Chapter 5: Using Variables, Parameters, and Expressions 165

Dynamic Package Objects 166

Variable Overview 166

Parameter Overview 166

Expression Overview 167

Understanding Data Types 168

SSIS Data Types 168

Date and Time Type Support 170

How Wrong Data Types and Sizes Can Affect Performance 171

Unicode and Non-Unicode Conversion Issues 171

Casting in SSIS Expressions 173

Using Variables and Parameters 174

Defining Variables 174

Defining Parameters 175

Variable and Parameter Data Types 176

Working with Expressions 177

C#-Like? Close, but Not Completely 178

The Expression Builder 179

Syntax Basics 180

Using Expressions in SSIS Packages 194

Summary 204

Chapter 6: Containers 205

Task Host Containers 205

Sequence Containers 206

Groups 207

For Loop Container 207

Foreach Loop Container 210

Foreach File Enumerator Example 211

Foreach ADO Enumerator Example 213

Summary 218

Chapter 7: Joining Data 219

The Lookup Transformation 220

Using the Merge Join Transformation 221

Contrasting SSIS and the Relational Join 222

Lookup Features 224

Building the Basic Package 225

Using a Relational Join in the Source 227

Using the Merge Join Transformation 230

Using the Lookup Transformation 235

Full-Cache Mode 235

No-Cache Mode 239

Partial-Cache Mode 240

Multiple Outputs 243

Expressionable Properties 246

Cascaded Lookup Operations 247

Cache Connection Manager and Cache Transform 249

Summary 252

Chapter 8: Creating an End-to-End Package 253

Basic Transformation Tutorial 253

Creating Connections 254

Creating the Control Flow 257

Creating the Data Flow 257

Completing the Package 259

Saving the Package 260

Executing the Package 260

Typical Mainframe ETL with Data Scrubbing 261

Creating the Data Flow 263

Handling Dirty Data 263

Finalizing 268

Handling More Bad Data 269

Looping and the Dynamic Tasks 271

Looping 271

Making the Package Dynamic 272

Summary 274

Chapter 9: Scripting in SSIS 275

Introducing SSIS Scripting 276

Getting Started in SSIS Scripting 277

Selecting the Scripting Language 277

Using the VSTA Scripting IDE 278

Example: Hello World 27