SQL Server 2005 Integration Services 101

In this video tutorial we kick off the new series with a high level overview of the purpose of SSIS, the tools and processes involved from developing to deploying Integration Services packages. We talk about Business Intelligence Development Studio and the Integration Service project template, the composition of packages, the major processes (Control Flow and Data Flow) and their relationship to the various types of components available from the toolbox.

In this tutorial we walk through a simple ETL (extract, transform and load) operation ... we'll extract data from the AdventureWorks database, we'll transform the data -- combining two columns into a new derived column -- finally, we'll load the transformed data into an Excel spreadsheet all the while paying special attention to the workflow within the Business Intelligence Development Studio.

In this tutorial we'll demonstrate how to deploy a finished package to a live production server and schedule it to run on the SQL Server Agent. The objective is to demonstrate how to close the loop from creating a simple Integrated Services package all the way to deploying it and then running it as a job to give you a good idea of the entire lifecycle of a project.

In this video tutorial we'll look at a short cut, or rather a quick start to creating packages using the Import / Export Wizard, as opposed to the way we built a package from scratch in video 2 of lesson 1, which was to create it all manually by hand using the designer and toolbox.

So, the key ideas here is understanding the role of each component in the data flow. Data Sources save connection information, Connection Managers can connect to the data sources, Data Flow Sources use the connection to load data into the dat flow buffer, Data Flow Transformations operate on data in the buffer and Data Flow Destinations move data back into the target. We also look at the basics of configuring Connection Manager and Data Flow Sources and talk about several properties of the Data Flow task related to tweaking SSIS memory buffer performace.

In this short video, I'll demonstrate how to create a global data source that works in the context of the entire solution, not just one package. Why would you want to do this? It is a common pattern in software development, network administration and life in general that you should save common data in one place and reference it so that if you ever need to change it, you only have to change it in one spot. If you have multiple packages in a solution, and they all use the same set of data sources wouldn't it be easier to modify a single setting if something changes? That's the basic premise for a global Data Source.

In this short video tutorial we'll look at creating a Data Source View, which works similarly to a global data source ... in fact, it uses a global data source -- but allows a developer or administrator to pair down the tables or views that can be seen and utilized by integration services. So, as a productivity measure it would help you see only those tables and views that are pertinent to the packages in your solution. As a security measure, I imagine that an administrator can make sure the integration services package developer can only see the tables that he needs for his job. It's just a way to define a subset of available tables and views for your packages in your solution.

In this video, using a data source view, we'll now create a named query and even add a calculated field to the mix. Why would we want to do this? Again, it comes down to managing a common set of data from a common source in one spot, so that -- assuming that multiple packages need the same set of refined data, and something changes with your common set of data, you're not chasing every reference in every package in the solution.

In this video we dig deeper into Data Flow Transformations re-examining the process of how transformations operate on the pipeline of data, and look at how different transformations require more computer resources than others, how different transformations work synchronously or asynchronously, etc. We also look at some common transformation properties and segment the transformations into logical groupings of functionality.

In this video we examine those transformations that act on the entire rowset at a time, including the Character Map Transformation, the Copy Column Transformation, the Data Converstion Transformation and the Derived Column Transformation, providing live examples for each.

The OLE DB Command Transformation allows you to execute a SQL command and even filling in parameters with columns from the current row. As you might imagine, this can be an expensive operation if you have a large number of rows, but it does provide a lot of power to your transformations.

In this video we look at those transformations that create new rowsets, which can include aggregate and sorted values, sample rowsets or pivoted and unpivoted rowsets. These are pretty expensive because they usually require a totally new chunk of memory to load new representations of the pipeline data into. Specifically we'll look at the Aggregate, Sort, Percentage Sampling and Row Sampling Transformations.

The purpose of a Pivot Transformation is to turn a relational table into a more flat table by turning rows into columns, while the Unpivot Transformation does the exact opposite. This tutorial demonstrates how to use these Transformations and how useful they can be when working with older data sources.

In this video we look at the Conditional Split and Multicast Split Transformations. Split transformations take a single pipeline and split it into multiple pipelines. The Conditional Split Transformation does this based on some conditions or checks on the data to determine which path it should go, while the Multicast makes an exact copy of the pipeline data into two or more pipelines.