| |
| |
Introduction | |
| |
| |
| |
Welcome To Sql Server Integration Services | |
| |
| |
SQL Server SSIS Historical Overview | |
| |
| |
What's New in SSIS | |
| |
| |
Tools of the Trade | |
| |
| |
Import and Export Wizard | |
| |
| |
The SQL Server Data Tools Experience | |
| |
| |
SSIS Architecture | |
| |
| |
Packages | |
| |
| |
Control Flow | |
| |
| |
Data Flow | |
| |
| |
Variables | |
| |
| |
Parameters | |
| |
| |
Error Handling and Logging | |
| |
| |
Editions of SQL Server | |
| |
| |
Summary | |
| |
| |
| |
The Ssis Tools | |
| |
| |
Import and Export Wizard | |
| |
| |
SQL Server Data Tools | |
| |
| |
Creating Your First Package | |
| |
| |
The Solution Explorer Window | |
| |
| |
The Toolbox | |
| |
| |
The Properties Windows | |
| |
| |
The SSIS Package Designer | |
| |
| |
Control Flow | |
| |
| |
Connection Managers | |
| |
| |
Variables | |
| |
| |
Data Flow | |
| |
| |
Parameters | |
| |
| |
Event Handlers | |
| |
| |
Package Explorer | |
| |
| |
Executing a Package | |
| |
| |
Management Studio | |
| |
| |
Summary | |
| |
| |
| |
Ssis Tasks | |
| |
| |
SSIS Task Objects | |
| |
| |
Using the Task Editor | |
| |
| |
The Task Editor Expressions Tab | |
| |
| |
SSDTCommon Properties | |
| |
| |
Looping and Sequence Tasks | |
| |
| |
Script Task (.NET) | |
| |
| |
Analysis Services Tasks | |
| |
| |
Analysis Services Execute DDL Task | |
| |
| |
Analysis Services Processing Task | |
| |
| |
Data Mining Query Task | |
| |
| |
Data Flow Task | |
| |
| |
Data Preparation Tasks | |
| |
| |
Data Profi ler | |
| |
| |
File System Task | |
| |
| |
Archiving a File | |
| |
| |
FTP Task | |
| |
| |
Getting a File Using FTP | |
| |
| |
Web Service Task | |
| |
| |
Retrieving Data Using the Web Service Task and XML Source Component | |
| |
| |
XML Task | |
| |
| |
Validating an XML File | |
| |
| |
RDBMS Server Tasks | |
| |
| |
Bulk Insert Task | |
| |
| |
Using the Bulk Insert Task | |
| |
| |
Execute SQL Task | |
| |
| |
Workfl ow Tasks | |
| |
| |
Execute Package Task | |
| |
| |
Execute Process Task | |
| |
| |
Message Queue Task | |
| |
| |
Send Mail Task | |
| |
| |
WMI Data Reader Task | |
| |
| |
WMI Event Watcher Task | |
| |
| |
Polling a Directory for the Delivery of a File | |
| |
| |
SMO Administration Tasks | |
| |
| |
Transfer Database Task | |
| |
| |
Transfer Error Messages Task | |
| |
| |
Transfer Logins Task | |
| |
| |
Transfer Master Stored Procedures Task | |
| |
| |
Transfer Jobs Task | |
| |
| |
Transfer SQL Server Objects Task | |
| |
| |
Summary | |
| |
| |
| |
Containers | |
| |
| |
Task Host Containers | |
| |
| |
Sequence Containers | |
| |
| |
Groups | |
| |
| |
For Loop Container | |
| |
| |
Foreach Loop Container | |
| |
| |
Foreach File Enumerator Example | |
| |
| |
Foreach ADO Enumerator Example | |
| |
| |
Summary | |
| |
| |
| |
The Data Flow | |
| |
| |
Understanding the Data Flow | |
| |
| |
Data Viewers | |
| |
| |
Sources | |
| |
| |
OLE DB Source | |
| |
| |
Excel Source | |
| |
| |
Flat File Source | |
| |
| |
Raw File Source | |
| |
| |
XML Source | |
| |
| |
ADO.NET Source | |
| |
| |
Destinations | |
| |
| |
Excel Destination | |
| |
| |
Flat File Destination | |
| |
| |
OLE DB Destination | |
| |
| |
Raw File Destination | |
| |
| |
Recordset Destination | |
| |
| |
SQL Server and Mobile Destinations | |
| |
| |
Data Mining Model Training | |
| |
| |
DataReader Destination | |
| |
| |
Dimension and Partition Processing | |
| |
| |
Common Transformations | |
| |
| |
Synchronous versus Asynchronous Transformations | |
| |
| |
Aggregate | |
| |
| |
Conditional Split | |
| |
| |
Data Conversion | |
| |
| |
Derived Column | |
| |
| |
Lookup | |
| |
| |
Row Count | |
| |
| |
Script Component | |
| |
| |
Slowly Changing Dimension | |
| |
| |
Sort | |
| |
| |
Union All | |
| |
| |
Other Transformations | |
| |
| |
Audit | |
| |
| |
Character Map | |
| |
| |
Copy Column | |
| |
| |
Data Mining Query | |
| |
| |
DQS Cleansing | |
| |
| |
Export Column | |
| |
| |
Fuzzy Lookup | |
| |
| |
Fuzzy Grouping | |
| |
| |
Import Column | |
| |
| |
Merge | |
| |
| |
Merge Join | |
| |
| |
Multicast | |
| |
| |
OLE DB Command | |
| |
| |
Percentage and Row Sampling | |
| |
| |
Pivot Transform | |
| |
| |
Unpivot | |
| |
| |
Term Extraction | |
| |
| |
Term Lookup | |
| |
| |
Data Flow Example | |
| |
| |
Summary | |
| |
| |
| |
Using Variables, Parameters, And Expressions | |
| |
| |
Dynamic Package Objects | |
| |
| |
Variable Overview | |
| |
| |
Parameter Overview | |
| |
| |
Expression Overview | |
| |
| |
Understanding Data Types | |
| |
| |
SSIS Data Types | |
| |
| |
Date and Time Type Support | |
| |
| |
How Wrong Data Types and Sizes Can Affect Performance | |
| |
| |
Unicode and Non-Unicode Conversion Issues | |
| |
| |
Casting in SSIS Expressions | |
| |
| |
Using Variables and Parameters | |
| |
| |
Defi ning Variables | |
| |
| |
Defining Parameters | |
| |
| |
Variable and Parameter Data Types | |
| |
| |
Working with Expressions | |
| |
| |
C#-Like? Close, but Not Completely | |
| |
| |
The Expression Builder | |
| |
| |
Syntax Basics | |
| |
| |
Using Expressions in SSIS Packages | |
| |
| |
Summary | |
| |
| |
| |
Joining Data | |
| |
| |
The Lookup Transformation | |
| |
| |
Using the Merge Join Transformation | |
| |
| |
Contrasting SSIS and the Relational Join | |
| |
| |
Lookup Features | |
| |
| |
Building the Basic Package | |
| |
| |
Using a Relational Join in the Source | |
| |
| |
Using the Merge Join Transformation | |
| |
| |
Using the Lookup Transformation | |
| |
| |
Full-Cache Mode | |
| |
| |
No-Cache Mode | |
| |
| |
Partial-Cache Mode | |
| |
| |
Multiple Outputs | |
| |
| |
Expressionable Properties | |
| |
| |
Cascaded Lookup Operations | |
| |
| |
Cache Connection Manager and Cache Transform | |
| |
| |
Summary | |
| |
| |
| |
Creating An End-To-End Package | |
| |
| |
Basic Transformation Tutorial | |
| |
| |
Creating Connections | |
| |
| |
Creating the Control Flow | |
| |
| |
Creating the Data Flow | |
| |
| |
Completing the Package | |
| |
| |
Saving the Package | |
| |
| |
Executing the Package | |
| |
| |
Typical Mainframe ETL with Data Scrubbing | |
| |
| |
Creating the Data Flow | |
| |
| |
Handling Dirty Data | |
| |
| |
Finalizing | |
| |
| |
Handling More Bad Data | |
| |
| |
Looping and the Dynamic Tasks | |
| |
| |
Looping | |
| |
| |
Making the Package Dynamic | |
| |
| |
Summary | |
| |
| |
| |
Scripting In Ssis | |
| |
| |
Introducing SSIS Scripting | |
| |
| |
Getting Started in SSIS Scripting | |
| |
| |
Selecting the Scripting Language | |
| |
| |
Using the VSTA Scripting IDE | |
| |
| |
Example: Hello World | |
| |
| |
Adding Code and Classes | |
| |
| |
Using Managed Assemblies | |
| |
| |
Example: Using Custom .NET Assemblies | |
| |
| |
Using the Script Task | |
| |
| |
Confi guring the Script Task Editor | |
| |
| |
The Script Task Dts Object | |
| |
| |
Accessing Variables in the Script Task | |
| |
| |
Connecting to Data Sources in a Script Task | |
| |
| |
Raising an Event in a Script Task | |
| |
| |
Writing a Log Entry in a Script Task | |
| |
| |
Using the Script Component | |
| |
| |
Diff erences from a Script Task | |
| |
| |
Confi guring the Script Component Editor | |
| |
| |
Accessing Variables in a Script Component | |
| |
| |
Connecting to Data Sources in a Script Component | |
| |
| |
Raising Events | |
| |
| |
Logging | |
| |
| |
Example: Data Validation | |
| |
| |
Synchronous versus Asynchronous | |
| |
| |
Essential Coding, Debugging, and Troubleshooting Techniques | |
| |
| |
Structured Exception Handling | |
| |
| |
Script Debugging and Troubleshooting | |
| |
| |
Summary | |
| |
| |
| |
Loading A Data Warehouse | |
| |
| |
Data Profiling | |
| |
| |
Initial Execution of the Data Profiling Task | |
| |
| |
Reviewing the Results of the Data Profiling Task | |
| |
| |
Turning Data Profi le Results into Actionable ETL Steps | |
| |
| |
Data Extraction and Cleansing | |
| |
| |
Dimension Table Loading | |
| |
| |
Loading a Simple Dimension Table | |
| |
| |
Loading a Complex Dimension Table | |
| |
| |
Considerations and Alternatives to the SCD Transformation | |
| |
| |
Fact Table Loading | |
| |
| |
SSAS Processing | |
| |
| |
Using a Master ETL Package | |
| |
| |
Summary | |
| |
| |
| |
Advanced Data Cleansing In Ssis | |
| |
| |
Advanced Derived Column Use | |
| |
| |
Text Parsing Example | |
| |
| |
Advanced Fuzzy Lookup and Fuzzy Grouping | |
| |
| |
Fuzzy Lookup | |
| |
| |
Fuzzy Grouping | |
| |
| |
DQS Cleansing | |
| |
| |
Data Quality Services | |
| |
| |
DQS Cleansing Transformation | |
| |
| |
Summary | |
| |
| |
| |
Using The Relational Engine | |
| |
| |
Data Extraction | |
| |
| |
SELECT * Is Bad | |
| |
| |
WHERE Is Your Friend | |
| |
| |
Transform during Extract | |
| |
| |
Many ANDs Make Light Work | |