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.