ioopl: 2014

In the name of Allah, the most gracious, the most merciful

In this example we are using SQL Server 2012, but its applicable for SQL Server 2008 too.

The whole idea is to see the Data transformation in action in SQL Server Integration Studio

We will be using the SQL Sever Adventure Works Database for this example. The database files can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/55330

For this example we will be using the AdventureWorksDW2012 Data File

http://msftdbprodsamples.codeplex.com/downloads/get/165405

Once downloaded, attach this database with SQL Server 2012 or 2008

What do we want to do:

The idea is to do a Transfer (or Fecth) the Adventure Works Dataware house tables to our own small database using the power of SQL Server Integration Services.

Step 1

Create a Database called , BITEDB1

Step 2

Now Create Table in the Database,

Simply right click the database

=> New Query

And Copy Paste this command in the Query Window and Hit F5 or Execute

--CREATE THE DIMDATE DIMENSION TABLE

CREATE TABLE dbo.dimDate

(

DateKey DATE NOT NULL,

DayNumberOfWeek TINYINT NOT NULL,

DayNumberOfMonth TINYINT NULL,

DayNumberOfYear SMALLINT NULL,

WeekNumberOfYear TINYINT NULL,

CalendarQuarter TINYINT NULL,

CalendarYear SMALLINT NULL,

EnglishMonthName VARCHAR(15) NULL,

CONSTRAINT PK_dimDates PRIMARY KEY(DateKey)

);

--CREATE THE DIMCUSTOMER DIMENSION TABLE

CREATE TABLE dbo.dimCustomer

(

CustomerKey INT NOT NULL,

Title VARCHAR(8) NULL,

Name VARCHAR(100) NULL, -- Concatenate First & Last Name when loading....

BirthDate Date NULL,

Age AS

CASE

WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP)<=40 THEN 'Younger'

WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP)>50 THEN 'Older'

ELSE 'Middle Age'

END,

Gender CHAR(1) NULL,

AnnualIncome MONEY NULL,

CONSTRAINT PK_dimCustomers PRIMARY KEY(CustomerKey)

);

--CREATE THE DIMPRODUCT DIMENSION TABLE

CREATE TABLE dbo.dimProduct

(

ProductKey INT NOT NULL,

EnglishProductName VARCHAR(50) NOT NULL,

Color VARCHAR(20) NULL,

ListPrice MONEY NULL,

StandardCost MONEY NULL,

DaysToManufacture INT NULL,

Weight FLOAT NULL,

ReOrderPoint SMALLINT NULL,

CONSTRAINT PK_dimProducts PRIMARY KEY(ProductKey)

);

--CREATE THE FACTINTERNETSALES FACT TABLE

CREATE TABLE dbo.FactInternetSales

(

InternetSalesKey INT NOT NULL IDENTITY(1,1),

ProductKey INT NOT NULL,

CustomerKey INT NOT NULL,

SalesOrderNumber VARCHAR(20) NOT NULL,

SalesOrderLineNumber TINYINT NOT NULL,

OrderQuantity SMALLINT NOT NULL,

UnitPrice MONEY NOT NULL,

ExtendedAmount MONEY NOT NULL,

TaxAmt MONEY NOT NULL,

OrderDate DATE NOT NULL,

CONSTRAINT PK_factIntSales PRIMARY KEY(InternetSalesKey)

);

Step 3

Create the Database Diagram by using the above created 4 tables in SQL Server Management Studio.

Expand BiteDB1 database and Right Click on Database Diagram Folder and Select New Database Diagram

If you click on the Database Diagram folder and Get and Error like this, then don't worry and just click Yes

Step 4

Jump over to SQL Server Data Tools Studio

File => New => Project => Select Integration Services Project

And Click OK

Step 5

Right click inside the bottom pane of the SSIS project, where it says Connection Manager,

And Select New OLE DB Connection and then NEW

Step 6

If the source and destination databases are not there, then click on NEW and bring in the source and destination databases.

You have to repeat Step 6, once for the Source database connection, and then for the destination database selection

Fill in the server and database details.

Notice below the Connection Managers

Step 7 (Loading Target Dimension Table)

Inside the Control Flow Tab,

Drag and Drop an Execute SQL Task component onto the Control Flow Tab

and SELECT THE DESTINATION DATABASE.

This would be the target Database

Step 8

Double click on the Execute SQL Task component

and set values for Connection, SQLStatement , also set the BypassPrepare to False

Note : If you get an error at this stage this is because of the Foreign Key Constraint, Remove the Foreign Key in the SQL Server Database Diagram on the Table and it should work fine and the red cross error will disappear.

Step 9

Drag in a Data Flow Task Component from the Toolbox on to the Control Flow Pane.

Step 10

Double click on it

Notice the change of interface when you double click on it. As it switches to Data Flow Tab.

Step 11

Drag and Drop an OLE DB Source component from the Toolbox onto the Data Flow pane.

Step 12

Double click on the OLE DB Source Component.

Once the window opens up,

Select the source table in the Name of the table or the view: from which you would like to extract the data from.

In this example we are using dboProduct Table

Step 13

Click on the Columns Tab on the left side of the OLE DB Source Editor Panel and check the columns of table from which you would like to extract the data. And click OK.

Step 14

Next Drag and Drop an OLE DB Destination Component from the Toolbox onto the Data Flow Pane and

REPEAT Step 13 to set the connection of Destination Table in the OLE DB Destination component.

Step 15

Now SET the connection between the OLE DB Source and OLE DB Destination.

Drag and Drop the green or blue arrow from the OLE DB Source onto the OLE DB Destination Component.

Step 16

At this stage we can view the destination between the source and destination table by clicking on the Mapping Tab on the left side of the OLE DB Destination pane.

And click OK

Step 17

At this stage if you see an error on the OLE DB Destination Component that means there is some data type mismatch between the source and destination table.

In order to fix this we would make use of a Data Conversion Component

Step 18

Drag and Drop a Data Conversion Component from the toolbox onto the Data Flow Pane and Link OLE DB Source with the Data Conversion Component and Data Conversion Component with OLE DB Destination Component