Integration Services Projects

Integration Services is the ETL tool for SQL Server 2012. ETL means Extraction, Transformation and Loading. In other words: A tool for migrating data from a data source to a data destination. In this course we will use Integration Services to migrate data from the OLTP systems into an intermediate relational platform and also to migrate data from the intermediate relational platform to the OLAP database. Integration Services packages may furthermore be executed at fixed intervals, ensuring an automatic update and migration of analytical data.

It will be demonstrated how to build an SSIS project from ground up. This will include definition of overall Control Flows as well as the detailed Data Flows. The purpose of the initial SSIS package in this class will be to automate the migration of the four tables from our CHRONUS system that we have used to build our initial cube.

The migration will be fully automated when we have scheduled an SQL Server Agent job to be executed at fixed intervals. This part will also be demonstrated.

Adding SSIS Project to current Solution

First we need to set up the framework for an SSIS project in our current TDWD1 Solution. This is done by adding a new project to the solution that is currently opened in Microsoft Visual Studio.

The type of project we want to add is an Integration Services Project. To keep to the naming convention, we would like to call it TDWD-1-Integration Services Project.

This will result in the SSIS project being created, and the new project should be immediately visible in the Solution Explorer.

Now we must start building the content of the SSIS package. The design window is displaying the Control Flow tab of the package.

Point to the SSIS Toolbox icon in the left hand side of the screen and drag the Data Flow Task item into the design area.

Right click the Data Flow Task item to rename it to Transfer Sales Tables. Then double click it to go to the Data Flow tab of the Design window.