An in-depth look at the radical changes to the newest release of SISS
Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.
* Details the newest features of the 2012 SISS product release, which is the most significant release since 2005
* Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements
* Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques
Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.
Autorentext
Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpstartTV.com.
Erik Veerman is a consultant, author, trainer, and a mentor with SolidQ. His award-winning industry recognition includes Microsoft's BI Solution of the Year and SQL Server Magazine's Innovator Cup.
Jessica M. Moss is a well-known architect, speaker, author, and Microsoft MVP of SQL Server business intelligence who has created data warehousing solutions for a variety of industries.
Mike Davis, MCTS, MCITP, is the Managing Project Lead at Pragmatic Works. He has co-authored several business intelligence books and regularly speaks at SQL events.
Chris Rock is a software developer and program manager at Pragmatic Works, where he develops custom SSIS components and software.
Zusammenfassung
An in-depth look at the radical changes to the newest release of SISS
Microsoft SQL Server 2012 Integration Services (SISS) builds on the revolutionary database product suite first introduced in 2005. With this crucial resource, you will explore how this newest release serves as a powerful tool for performing extraction, transformation, and load operations (ETL). A team of SQL Server experts deciphers this complex topic and provides detailed coverage of the new features of the 2012 product release. In addition to technical updates and additions, the authors present you with a new set of SISS best practices, based on years of real-world experience that have transpired since the previous edition was published.
- Details the newest features of the 2012 SISS product release, which is the most significant release since 2005
- Addresses the keys to a successful ETL solution, such as using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements
- Includes additional case studies and tutorial examples to illustrate advanced concepts and techniques
Professional Microsoft SQL Server 2012 Integration Services is a valuable resource that meets the demands and high expectations of experienced SSIS professionals.
Inhalt
INTRODUCTION xxix
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 2
Import and Export Wizard 3
The SQL Server Data Tools Experience 4
SSIS Architecture 5
Packages 5
Control Flow 6
Data Flow 9
Variables 13
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 23
Creating Your First Package 25
The Solution Explorer Window 26
The Toolbox 27
The Properties Windows 28
The SSIS Package Designer 28
Control Flow 29
Connection Managers 32
Variables 32
Data Flow 34
Parameters 34
Event Handlers 34
Package Explorer 35
Executing a Package 36
Management Studio 36
Summary 37
CHAPTER 3: SSIS TASKS 39
SSIS Task Objects 40
Using the Task Editor 40
The Task Editor Expressions Tab 40
SSDTCommon Properties 41
Looping and Sequence Tasks 42
Script Task (.NET) 43
Analysis Services Tasks 45
Analysis Services Execute DDL Task 45
Analysis Services Processing Task 46
Data Mining Query Task 46
Data Flow Task 47
Data Preparation Tasks 48
Data Profi ler 48
File System Task 50
Archiving a File 52
FTP Task 53
Getting a File Using FTP 54
Web Service Task 55
Retrieving Data Using the Web Service Task and XML Source Component 57
XML Task 60
Validating an XML File 62
RDBMS Server Tasks 64
Bulk Insert Task 64
Using the Bulk Insert Task 67
Execute SQL Task 68
Workfl ow Tasks 80
Execute Package Task 80
Execute Process Task 81
Message Queue Task 82
Send Mail Task 83
WMI Data Reader Task 84
WMI Event Watcher Task 86
Polling a Directory for the Delivery of a File 86
SMO Administration Tasks 87
Transfer Database Task 88
Transfer Error Messages Task 89
Transfer Logins Task 89
Transfer Master Stored Procedures Task 90
Transfer Jobs Task 91
Transfer SQL Server Objects Task 91
Summary 92
CHAPTER 4: CONTAINERS 93
Task Host Containers 93
Sequence Containers 94
Groups 95
For Loop Container 95
Foreach Loop Container 97
Foreach File Enumerator Example 98
Foreach ADO Enumerator Example 100
Summary 103
CHAPTER 5: THE DATA FLOW 105
Understanding the Data Flow 105
Data Viewers 106
Sources 106
OLE DB Source 107
Excel Source 109
Flat File Source 110
Raw File Source 114
XML Source 115
ADO.NET Source 115
Destinations 115
Excel Destination 116
Flat File Destination 116
OLE DB Destination 116
Raw File Destination 117
Recordset Destination 117
SQL Server and Mobile Destinations 118
Data Mining Model Training 118
DataReader Destination 118
Dimension and Partition Processing 118
Common Transformations 119
Synchronous versus Asynchronous Transformations 119
Aggregate 119
Conditional Split 121
Data Conversion 122
Derived Column 122
Lookup 123
Row Count 124
Script Component 1...