SQL Server 2008 Integration Services

This course assumes no prior knowledge of SQL Server Integration Services. This course does assume prior knowledge of SQL Server 2008 and the use of SQL Server Management Studio for development and administrative tasks.

You should be able to create CRUD (create, retrieve, update, and delete) queries using T-SQL, understand basic relational databases design, run script files and diagnose problems that occur, and have experience building applications that access data stored in SQL Server.

You must also know how to connect to an instance of SQL Server 2008 using the various connection dialog boxes in Management Studio and development tools.

1. A Guided Tour of Integration Services 

Understanding Integration Services 
What is ETL? 
Integration Services Packages 
Tools for Building Integration Services Packages 
Integration Services Package Storage Options 
Creating a Package with the Import and Export Wizard 
Other Ways to Launch the Import and Export Wizard 
Exploring and Executing an Integration Services Package in BIDS 
The BIDS Interface and Components 
Executing a Package in BIDS 
Exploring and Executing a Package Outside of BIDS 

2. Control Flow 

Overview of Control Flow in Integration Services 
Elements in a Control Flow 
Control Flow Tasks 
Working with Workflow Tasks 
The Execute SQL Task 
File System Task 
FTP Task 
Send Mail Task 
Precedence Constraints 
Why Use Precedence Constraints? 
Implementing Precedence Constraints 
Recommendations 

3. Data Flows 

The Data Flow Task 
Data Flow Pipeline 
Data Flow Sources 
Data Flow Destination 
Data Viewers 
Data Flow Transformations 
Row Transformations 
Rowset Transformations 
Split and Join Transformations 
Business Intelligence Transformations 
Other Transformations 

4. Variables and Configurations 

Understanding Variables 
Variable Properties 
Variable Data Types 
Variable Scope 
Where Can You Use Variables? 
Scope and the Execute Package Task 
Using Variables in Control Flow 
Execute SQL Task 
File System Task 
Precedence Constraint Expressions 
Property Expressions 
Send Mail Task 
Foreach Loop Container 
Script Task 
Using Variables in Data Flow 
Connection Managers 
Derived Column Transformation 
Conditional Split Transformation 
Parameters 
Configurations 
Using Configurations 
Configuration Storage 
Using Variables and Configurations Between Packages 
Parent and Child Scenarios 
Direct Configuration 
Indirect Configuration 

5. Advanced Control Flow 

Advanced Control Flow Overview 
Using Containers 
Container Properties 
Task Host Container 
Sequence Container 
Foreach Loop Container 
For Loop Container 
Grouping Container 
Transaction Support in Integration Services 

6. Error Handling and Logging 

When Things Go Wrong 
Checkpoints 
Handling Errors and Debugging 
Error Handling in Control Flows with Precedence Constraints 
Error Outputs in Data Flows 
Breakpoints 
Package Logging 
Configuring Package Logging 
Logging to a Text File 
Logging in Windows Event Log 
Custom Logging 
Event Handling 

7. Advanced Data Flow 

Synchronous Transformations 
Asynchronous Transformations 
Using Advanced Transformations 
Audit Transformation 
Multicast Transformation 
Derived Column Transformation 
Union All Transformation 
Merge Transformation 
Lookup Transformation 
Fuzzy Lookup Transformation 
Conditional Split Transformation 
Handling Slowly Changing Dimensions 
Loading a Data Warehouse 
The Slowly Changing Dimension Transformation 

8. Package Deployment 

Deploying Packages 
Deployment Challenges 
Create a Package Deployment Utility 
Deployment Utility Properties 
Deployment Folder 
Deployment Manifest 
Installing a Package 
The Package Installation Wizard 
Deploy a Package to the File System 
Deploy a Package to SQL Server 
Importing a Package Using Management Studio 
Should You Deploy to SQL Server or the File System? 
Redeploying Updated Packages 

9. Package Management 

Overview of Package Management 
Managing Integration Services Packages 
Managing Package with DTUtil 
Managing Packages with Management Studio 
Executing Packages 
DTExecUI: The Execute Package Utility 
Executing Packages at the Command Line with DTExec 
Scheduling Package Execution with SQL Server Agent 
Integration Services Security 
Protecting Sensitive Package Information 
Controlling Access to Packages Stored in SQL Server 
Signing Packages with Digital Certificates 

10. Scripting and Custom Components 

Extending Integration Services Capabilities Through Code 
Integration Services Scripting 
Custom Component Development 
Integration Services Scripting 
Integration Services Object Model 
Visual Studio Tools for Applications Script Editor 
Scripting in Control Flows with the Script Task 
Variables 
Going Beyond Built-In Tasks 
Scripting in Data Flows with the Script Component 
Input and Output Columns 
Script Component Types 
Synchronous and Asynchronous Transformations 
Going Beyond Built-In Data Flow Components 
Custom Integration Services Components 
Custom Component Development 
Third-Party Integration Services Components 

11. Best Practices 

Best Practices for Using Integration Services 
Keep it Simple 
Best Practices for Package Development and Design 
Development Standards 
Package Design Best Practices 
Error Handling and Logging 
Data Flow Best Practices 
Data Flow Transformation Performance, Maintenance, and Ease of Use 
Data Flow Destinations 
Integration Services Engine Best Practices 
Deployment and Management Best Practices 
Deployment Best Practices 
Security Best Practices 
Management Best Practices 

12. Going Beyond ETL 

Using Integration Services Beyond ETL 
Migrating and Maintaining SQL Servers with Integration Services 
SQL Server Transfer Tasks 
SQL Server Maintenance Plans 
Working with Analysis Services 
The Analysis Services Processing Task 
Dimension and Partition Processing Destinations 
Analysis Services Execute DDL Task 
Data Mining Query Task 
Slowly Changing Dimension Transformation 
Working with Windows Management Instrumentation 
The WMI Data Reader Task 
The WMI Event Watcher Task

The Modulus Approach

Modulus provide high level content rich courses catering for the latest needs of analysts, software developers and test engineers in the financial, gaming and military simulations markets. Workshops are mostly delivered in a bespoke content specific format, for each of our clients but a selection of the most popular topics are being offered in Canary Wharf as public courses.

The Modulus lecturing team consists of industry experts who have a wealth of real world experience; they add the flavour to our courses with creative delivery styles and highly useful examples.

We promise you’re learning experience will feel different. Most companies assume that training is simple, as in essence it only has a few deliverables. Modulus does not take this view and aim to flip the whole training experience on its head. Strong emphasis is placed on each of these deliverables from pre course evaluation to course administration and final delivery. We include many features that companies consider optional extras and go that extra mile to make sure your courses are relevant and content rich before delivery.

Why Modulus?

With so many training vendors popping up providing price promises are options to retake courses if not 100% satisfied along with 100's of public courses why should you choose modulus. The answer is very simple we have a certain place within the market and are very good at what we do. If you want to focus on a specific problem but just can't find the right course then you need to be speaking to us. If you are looking for public places for your Cisco engineers then we would kindly decline.

Modulus Bespoke

Additionally, Modulus offer bespoke programmes which are designed as an iterative experience. This works in the way that we firstly look at the project need / problem or the skills gap, then the content is  presented as a discussion point before a final course outline is drafted. Finally this is remodeled to reflect building blocks, pace and course flow as a final tailored outlines.

Learn more about Modulus Training and how their professional IT training courses can help you here.