Professional SQL Server 2005 Integration Services,9780764584350

Professional SQL Server 2005 Integration Services

by ; ; ; ; ; ; ; ; ;
ISBN13: 9780764584350
ISBN10: 0764584359
Format: Paperback
Pub. Date: 2006-01-31
Publisher(s): Wrox
  • Free Shipping Icon

    This Item Qualifies for Free Shipping!*

    *Excludes marketplace orders.

List Price: $52.49

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $3.63

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

This book will help you get past the initial learning curve quickly so that you can get started using SSIS to transform data, create a workflow, or maintain your SQL Server. Offering you hands-on guidance, you'll learn a new world of integration possibilities and be able to move away from scripting complex logic to programming tasks using a full-featured language.What you will learn from this book Ways to quickly move and transform data How to configure every aspect of SSIS How to interface SSIS with web services and XML Techniques to scale the SSIS and make it more reliable How to migrate DTS packages to SSIS How to create your own custom tasks and user interfaces How to create an application that interfaces with SSIS to manage the environment A detailed usable case study for a complete ETL solution Who this book is forThis book is for developers, DBAs, and users who are looking to program custom code in all of the .NET languages. It is expected that you know the basics of how to query the SQL Server and have some fundamental programming skills.Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

Author Biography

Brian Knight, SQL Server MVP, MCSE, MCDBA, is the cofounder of SQLServerCentral.com and was recently on the Board of Directors for the Professional Association for SQL Server (PASS). He runs the local SQL Server users group in Jacksonville, Florida (JSSUG). Brian is a contributing columnist for SQL Server Standard and also maintains a weekly column for the database Web site SQLServerCentral.com. He is the author of Admin911: SQL Server (Osborne/McGraw-Hill Publishing) and coauthor of Professional SQL Server DTS and Professional SQL Server 2005 SSIS (Wiley Publishing). Brian has spoken at such conferences as PASS, SQL Connections, and TechEd. His blog can be found at www.whiteknighttechnology.com.

Allan Mitchell is joint owner of a UK-based consultancy, Konesans, specializing in ETL implementation and design. He is currently working on a project for one of the UK’s leading investment banks doing country credit risk profiling as well as designing custom SSIS components for clients.

Darren Green is the joint owner of Konesans, a UK-based consultancy specializing in SQL Server, and of course DTS and SSIS solutions. Having managed a variety of database systems from version 6.5 onwards, he has extensive experience in many aspects of SQL Server. He also manages the resource sites SQLDTS.com and SQLIS.com, as well as being a Microsoft MVP.

Douglas Hinson, MCP splits his time between database and software development as a Senior Architect for Hinson & Associates Consulting in Jacksonville, Florida. Douglas specializes in conceptualizing and building insurance back-end solutions for payroll deduction, billing, payment, and claims processing operations in a multitude of development environments. He also has experience developing logistics and postal service applications.

Kathi Kellenberger is a database administrator at Bryan Cave LLP, an international law firm headquartered in St. Louis, Missouri. She fell in love with computers the first time she used a Radio Shack TRS-80, many years ago while in college. Too late to change majors, she spent 16 years in a health care field before switching careers. She lives in Edwardsville, Illinois, with her husband, Dennis, college-age son, Andy, and many pets. Her grown-up daughter, Denise, lives nearby. When she’s not working or writing articles for SQLServerCentral.com, you’ll find her spending time with her wonderful sisters, hiking, cycling, or singing at the local karaoke bar.

Andy Leonard is a SQL Server DBA, MCSD, and engineer who lives in Jacksonville, Florida. Andy manages a SQL Server DBA Team. He has a passion for developing enterprise solutions of all types and a fondness for business intelligence solutions in industrial enterprises. Learn more at www.andyleonard.net and reach Andy at andy@andyleonard.net.

Erik Veerman is a mentor with Solid Quality Learning and is based out of Atlanta, Georgia. Erik has been developing Microsoft-based Business Intelligence and ETL-focused solutions since the first release of DTS and OLAP Server in SQL Server 7.0, working with a wide range of customers and industries. His industry recognition includes Microsoft’s Worldwide BI Solution of the Year and SQL Server Magazine’s Innovator Cup winner. Erik led the ETL architecture and design for the first production implementation of Integration Services and participated in developing ETL standards and best practices for Integration Services through Microsoft’s SQL Server 2005 reference initiative, Project REAL.

Jason Gerard is President of Object Future Consulting, Inc., a software development and mentoring company located in Jacksonville, Florida (www.objectfuture.com). Jason is an expert with .NET and J2EE technologies and has developed enterprise applications for the health care, financial, and insurance industries. When not developing enterprise solutions, Jason spends as much time as possible with his wife Sandy, son Jakob, and Tracker, his extremely lazy beagle.

Haidong Ji, MCSD and MCDBA, is a Senior Database Administrator in Chicago, Illinois. He manages enterprise SQL Server systems, along with some Oracle and MySQL systems on Unix and Linux. He has worked extensively with DTS 2000. He was a developer prior to his current role, focusing on Visual Basic, COM and COM+, and SQL Server. He is a regular columnist for SQLServerCentral.com, a popular and well-known portal for SQL Server.

Mike Murphy is a .NET developer, MCSD, and in a former life an automated control systems engineer currently living in Jacksonville, Florida. Mike enjoys keeping pace with the latest advances in computer technology, meeting with colleagues at Jacksonville Developer User Group meetings (www.jaxdug.com) and, when time allows, flying R/C Helicopters. To contact Mike, e-mail him at mike@murphysgeekdom.com or visit www.murphysgeekdom.com.

Table of Contents

Acknowledgments xi
Foreword xxiii
Introduction xxv
Who This Book Is For xxv
How This Book Is Structured xxv
What You Need to Use This Book xxvi
Conventions xxvii
Source Code xxvii
Errata xxvii
p2p.wrox.com xxviii
Welcome to SQL Server Integration Services
1(18)
What's New in SQL Server 2005 SSIS
1(2)
Import and Export Wizard
2(1)
The Business Intelligence Development Studio
3(1)
Architecture
3(6)
Packages
5(1)
Tasks
5(1)
Data Source Elements
6(2)
Data Source Views
8(1)
Precedence Constraints
9(1)
Constraint Value
9(1)
Conditional Expressions
9(1)
Containers
10(1)
Variables
10(1)
Data Flow Elements
11(3)
Sources
11(1)
Destinations
12(1)
Transformations
13(1)
Error Handling and Logging
14(2)
Editions of SQL Server 2005
16(1)
Summary
17(2)
The SSIS Tools
19(24)
Import and Export Wizard
19(6)
Using the Import and Export Wizard
19(6)
Package Installation Wizard
25(1)
Business Intelligence Development Studio
25(2)
Creating Your First Package
27(1)
The Solution Explorer Window
28(4)
The Toolbox
29(1)
The Properties Windows
30(1)
Navigation Pane
31(1)
Other Windows
32(1)
The SSIS Package Designer
32(9)
Controller Flow
33(3)
Connection Managers
36(1)
Variables
37(1)
Data Flow
38(1)
Event Handlers
39(1)
Package Explorer
40(1)
Executing a Package
41(1)
Summary
41(2)
SSIS Tasks
43(30)
Shared Properties
43(1)
Execute SQL Task
44(2)
Bulk Insert Task
46(2)
Using the Bulk Insert and Execute SQL Tasks
48(3)
Data Flow Task
51(1)
Execute Process Task
51(1)
File System Task
52(2)
FTP Task
54(1)
Using the File System and FTP Task
55(4)
Execute Package Task
59(1)
Script and ActiveX Tasks
60(2)
Send Mail Task
62(1)
Message Queue Task
63(1)
Web Service Task
63(2)
WMI Data Reader and Event Watcher Task
65(3)
XML Task
68(2)
SQL Server Analysis Services Execute DDL and Processing Tasks
70(1)
Data Mining Query Task
71(1)
The Expression Page
72(1)
Summary
72(1)
Containers and Data Flow
73(46)
Containers
73(7)
Task Host Containers
73(1)
Sequence Containers
73(1)
For Loop Container
74(4)
Foreach Loop Container
78(2)
Sources
80(4)
OLE DB Source
80(3)
Excel Source
83(1)
Flat File Source
84(1)
Raw File Source
84(1)
XML Source
84(1)
Data Reader Source
84(1)
Destinations
84(6)
Data Mining Model Training
86(1)
DataReader Destination
86(1)
Dimension and Partition Processing
87(1)
Excel Destination
87(1)
Flat File Destination
88(1)
OLE DB Destination
88(1)
Raw File Destination
89(1)
Recordset Destination
89(1)
SQL Server and Mobile Destinations
90(1)
Transformations
90(20)
Aggregate
91(2)
Audit
93(1)
Character Map
94(1)
Conditional Split
94(3)
Copy Column
97(1)
Data Conversion
97(1)
Data Mining Query
98(1)
Derived Column
99(1)
Export Column
100(1)
Fuzzy Grouping and Lookup
101(1)
Import Column
101(1)
Lookup
101(1)
Merge
102(1)
Merge Join
103(1)
Multicast
104(1)
OLE DB Command
105(1)
Percentage and Row Sampling
105(1)
Pivot and Unpivot
106(1)
Row Count
107(1)
Script Component
107(1)
Slowly Changing Dimension
107(1)
Sort
107(1)
Term Extraction and Lookup
108(1)
Union All
109(1)
Data Flow Example
110(6)
Data Viewers
116(1)
Summary
117(2)
Creating an End-to-End Package
119(24)
Basic Transformation Tutorial
119(6)
Creating Connections
120(2)
Creating the Tasks
122(1)
Creating the Data Flow
123(1)
Completing the Package
124(1)
Saving the Package
125(1)
Executing the Package
125(1)
Typical Mainframe ETL with Data Scrubbing
125(13)
Creating the Data Flow
129(1)
Handling Dirty Data
129(4)
Finalizing
133(1)
Handling More Bad Data
134(4)
Looping and the Dynamic Task
138(4)
Looping
139(1)
Making the Package Dynamic
140(2)
Summary
142(1)
Advanced Tasks and Transforms
143(64)
Execute SQL Task
144(6)
Variables
145(1)
Expressions
146(2)
Using SQL Output Parameters to Change Runtime Settings
148(2)
Import Column
150(7)
Import Column Example
151(4)
Import Column Example Using File Iteration
155(2)
Using Temp Tables in SSIS Package Development
157(2)
Export Column
159(1)
Row Count
160(2)
OLE DB Command
162(3)
Term Extraction
165(6)
Term Lookup
171(2)
Fuzzy Lookup
173(5)
Fuzzy Grouping
178(4)
Pivot Transform
182(4)
Unpivot
186(3)
Slowly Changing Dimension
189(12)
Database Object-Level Tasks
201(4)
Transfer Database Task
201(1)
Transfer Logins Task
202(1)
Transfer Master Stored Procedures Task
203(1)
Transfer SQL Server Objects Task
204(1)
Summary
205(2)
Scripting in SSIS
207(32)
Scripting Overview
207(1)
Expressions
208(5)
Dynamic Properties
208(3)
Expressions in Tasks
211(2)
Script Tasks
213(12)
The Dts Object
218(1)
Accessing Variables
219(1)
Events
220(2)
Logging
222(1)
Script Task Debugging
222(3)
Using .NET Assemblies
225(2)
Structured Exception Handling
227(1)
Script Component
228(9)
Using the Script Component
229(6)
Debugging the Script Component
235(2)
Summary
237(2)
Accessing Heterogeneous Data
239(40)
Excel
240(10)
Exporting to Excel
240(4)
Importing from Excel
244(6)
Access
250(9)
Understanding Access Security
250(1)
Configuring an Access Connection Manager
251(2)
Importing from Access
253(2)
Using a Parameter
255(4)
Oracle
259(3)
Client Setup
259(1)
Importing Oracle Data
259(3)
Web Services
262(10)
The Hyperlink Extractor Service
262(3)
The Currency Conversion Service
265(7)
XML Data
272(6)
Summary
278(1)
Reliability and Scalability
279(32)
Restarting Packages
279(13)
Simple Control Flow
280(5)
Containers within Containers and Checkpoints
285(3)
Variations on a Theme
288(2)
Inside the Checkpoint File
290(2)
Package Transactions
292(9)
Single Package, Single Transaction
293(3)
Single Package, Multiple Transactions
296(2)
Two Packages, One Transaction
298(1)
Single Package Using a Native Transaction in SQL Server
299(2)
Error Outputs
301(3)
Scaling Out
304(6)
Scale Out Memory Pressures
304(1)
Scale Out by Staging Data
305(5)
Summary
310(1)
Understanding the Integration Services Engine
311(30)
The Integration Services Engine: An Analogy
311(1)
Understanding the SSIS Data Flow and Control Flow
312(3)
Comparing and Contrasting the Data Flow and Control Flow
312(2)
SSIS Package Execution Image from Package Start to Package Finish
314(1)
Enterprise Workflows with the Control Flow
315(2)
Enterprise Data Processing with the Data Flow
317(22)
Memory Buffer Architecture
317(1)
Types of Transformations
318(11)
Advanced Data Flow Execution Concepts
329(10)
Summary
339(2)
Applying the Integration Services Engine
341(30)
That Was Then: DTS
341(5)
DTS Solution Architecture
342(1)
Common DTS Processing Practices
343(2)
DTS Limitations
345(1)
This Is Now: Integration Services
346(20)
Integration Services Design Practices
347(13)
Optimizing Package Processing
360(6)
Pipeline Performance Monitoring
366(3)
Summary
369(2)
DTS 2000 Migration and Metadata Management
371(20)
Migrating DTS 2000 Packages to SSIS
371(2)
Using the Package Migration Wizard
373(7)
Running DTS 2000 Packages under SSIS
380(4)
Package Metadata and Storage Management
384(5)
Managing SSIS Packages within SQL Server Management Studio
386(2)
Managing DTS 2000 Packages within SQL Server Management Studio
388(1)
Summary
389(2)
Error and Event Handling
391(26)
Precedence Constraints
391(7)
Precedence Constraints and Expressions
393(4)
Multiple Constraints
397(1)
Event Handling
398(10)
Events
398(1)
Inventory Example
399(7)
Event Bubbling
406(2)
Breakpoints
408(3)
Checkpoints
411(1)
Logging
412(4)
Summary
416(1)
Programming and Extending SSIS
417(50)
The Sample Components
417(2)
Component 1: Source Adapter
418(1)
Component 2: Transformation
418(1)
Component 3: Destination Adapter
419(1)
The Pipeline Component Methods
419(6)
Design-Time
419(4)
Runtime
423(2)
Connection Time
425(1)
Building the Components
425(36)
Preparation
426(6)
Building the Source Adapter
432(11)
Building the Transform
443(11)
Building the Destination Adapter
454(7)
Debugging Components
461(5)
Design-Time
462(1)
Runtime
463(3)
Summary
466(1)
Adding a User Interface to Your Component
467(24)
Three Key Steps
467(1)
Building the User Interface
468(13)
Adding the Project
468(3)
Implementing IDtsComponentUI
471(4)
Setting the UITypeName
475(1)
Building the Form
476(5)
Further Development
481(9)
Runtime Connections
482(2)
Component Properties
484(2)
Handling Errors and Warnings
486(2)
Column Properties
488(2)
Summary
490(1)
External Management and WMI Task Implementation
491(32)
External Management with Managed Code
491(1)
Application Object Maintenance Operations
492(13)
Package Maintenance Operations
493(1)
Server Folder Maintenance
494(1)
Package Role Maintenance
495(1)
Package Monitoring
495(1)
A Package Management Example
496(9)
Package Log Providers
505(4)
Specifying Events to Log
506(1)
Programming Log Providers
507(2)
Package Configurations
509(3)
Creating a Configuration
510(1)
Programming the Configuration Object
511(1)
Configuration Object
512(1)
Windows Management Instrumentation Tasks
512(10)
WMI Reader Task Explained
513(1)
WMI Event Watcher Task
514(1)
WMI Data Reader Example
515(6)
WMI Event Watcher Task Example
521(1)
Summary
522(1)
Using SSIS with External Applications
523(24)
RSS In, Reporting Services Report Out
524(8)
InfoPath Document
532(8)
ASP.NET Application
540(5)
Summary
545(2)
SSIS Software Development Life Cycle
547(46)
Introduction to Software Development Life Cycles
548(2)
Software Development Life Cycles: A Brief History
548(1)
Types of Software Development Life Cycles
549(1)
Versioning and Source Code Control
550(37)
Microsoft Visual SourceSafe
550(15)
Team Foundation Server, Team System, and SSIS
565(5)
MSF Agile and SSIS
570(3)
The Project Portal
573(1)
Putting It to Work
573(14)
Code Deployment and Promotion from Development to Test to Production
587(4)
The Deployment Wizard
588(1)
Import a Package
589(2)
Summary
591(2)
Case Study: A Programmatic Example
593(74)
Background
593(1)
Business Problem
594(1)
Solution Summary
594(1)
Solution Architecture
595(4)
Data Architecture
599(11)
File Storage Location Setup
599(1)
Bank ACH Payments
599(1)
Lockbox Files
600(1)
PayPal or Direct Credits to Corporate Account
601(1)
Case Study Database Model
601(1)
Database Setup
602(8)
Case Study Load Package
610(41)
Naming Conventions and Tips
611(1)
Package Setup and File System Tasks
612(4)
Lockbox Control Flow Processing
616(6)
Lockbox Validation
622(9)
Lockbox Processing
631(4)
ACH Control Flow Processing
635(4)
ACH Validation
639(4)
ACH Processing
643(2)
E-mail Payment Processing
645(3)
E-mail Data Flow Processing
648(3)
Testing
651(1)
Case Study Process Package
651(13)
Package Setup
652(2)
High-Confidence Data Flow
654(5)
Medium-Confidence Data Flow
659(4)
Interpreting the Results
663(1)
Running in SQL Agent
664(1)
Summary
665(2)
Index 667

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.