3.1. Hands-on Lab Preset

II. Configuration

• We will be using data from the AdventureWorks sample database which ships with SQL Server 2005.

3. Hands-on Lab I (Short data trouble shooting package)

I. Purpose of Hands-on Lab

• The purpose of this lab is to provide a quick sample/best practice on how to trouble shoot data within a package flow and to address common questions on ‘how to breakpoint and step through data in the flow’. We will load a contact table as the source data and parse out a portion of the email field, viewing the data to insure our parse expression is correct before connecting any ‘real’ destinations.
• This exercise will be more detailed than others on exactly what to click and type as a form of introduction, while the other labs will have more generalized instructions.
• This lab will also allow us to gauge the overall pre-knowledge of the students.

Setup and Congfiguration

In this Tutorial, we are explaining about SSIS by using following sample Databases.

1. SSIS Training Database - Download
2. SSIS Logging Database - Download

• Installation

1. Download the zip file from the download link given above.
2. Extract the zip file to the root c:\ so you end up with “C:\_SSIS_Training”.
3. Attach the following 2 databases. “SSISLOGGING.mdf” for audit and logging data (tables myfileaudit and ssis_ErrorRows) and database “SSISTRAINING.mdf” (tables mydescriptions) for data destination.
4. We will be using data from the AdventureWorks which ships with SQL 2005 (optional during installation)

• Sql Server Books Online (BOL)

1. The BOL topics noted through the doc with the icon above were found with BOL filtered to just search the “Technology” Integration Services, from the BOL Search Page.

• Miscellaneous

1. You may want to have a soft copy of this manual, which can be handy to have open while you do the labs, to allow you to copy/paste expressions from the manual to the Visual Studio design environment.

Audience and Pre-requisite knowledge

• Users understand the roles and differences between the applications "Business Intelligence Development Studio" and "SQL Server Management Studio".
• Users have basic understanding of SQL Server Integration Services functionality and for example have a basic understanding of the difference between "Control Flow" and "Data Flow" as well as know what "Log providers", "Connection Managers", "Property Expressions" do. I am looking for students to be familiar with the terms and the general idea of all of the things mentioned above. It’s ok if you have never played with them, just want terminology and general purpose to be familiar.
• SQL Server Business Intelligence studio is used to create, edit, trouble shoot packages while the command line tool ‘dtexec.exe’ is what you will execute packages with in production environments.

Someone can gear up on the above topics from the SSIS portal on MSDN.
http://msdn.microsoft.com/SQL/sqlwarehouse/SSIS/default.aspx
Where they can find links to the following two webcasts as well as other webcasts and articles.

• Introducing SQL Server Integration Services for SQL Server 2005 (Level 200)
• TechNet Webcast: Deploying, Managing and Securing Integration Services (Level 300)

Introduction

SQL Server Integration Services (SSIS), the successor to DTS in SQL Server 2005, is an all-new application that provides a data integration platform from easy-to-use tasks and transforms for the non-developer to a robust object model supporting creation of custom tasks data transformations. With the SSIS platform you can create solutions integrating data from non homogenous data sources, cleansing, and aggregation as well as work flow surrounding the data processing. SSIS goes beyond standard ETL processing (Extract Transform Load) providing components such as Web Service, XML, WMI tasks, and many more. Add to the rich list of out of the box components a full object model underneath, and users can create their own tasks, transformations, data sources/destinations, and Log Providers to suit almost any scenario.