Professional SQL Server 2005 Integration Services

by Knight, Brian; Mitchell, Allan; Green, Darren; Hinson, Douglas; Kellenberger, Kathi; Leonard, Andy; Veerman, Erik; Gerard, Jason; Ji, Haidong; Murphy, Mike

• ISBN13: 9780764584350
• ISBN10: 0764584359
• Format: Paperback
• Copyright: 2006-01-31
• Publisher: Wrox

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 [e-mail].

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 [e-mail] 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