Click here to Skip to main content
Click here to Skip to main content

Tagged as

SSIS: Load Data in Dimension Table Using SQL

, 15 Jan 2013 BSD
Rate this:
Please Sign up or sign in to vote.
Load data in Dimension Table using SQL.

Introduction

Populating data in dimension table is commonly known as upsert—in other words, update if exist, insert if not exist. It is a basic operation in data warehousing, and you will come across this operation many times when building a data warehouse ETL system. There are two other methods that are commonly used in practice to do upsert using SSIS. The first one is using a SQL statement, second one is using lookup or merge join, and third one is using SCD (Slowly changing Dimension). In this tutorial I will show how to load data in a Dimension table by using a SQL statement.

What you Need

This script was tested in SQL Server 2008.

Create Project

First create a database and table from script.

-- Create database
create database Test
go
use Test
go
 
-- Create customer table
if exists (select * from sys.tables where name = 'Customer')
drop table Customer
go
 
create table Customer
( 
CustomerId int not null primary key,
CustomerName varchar(30), DateOfBirth date, Town varchar(50),
TelephoneNo varchar(30), DrivingLicenceNo varchar(30), Occupation varchar(30)
)
go
 
-- Populate Customer Source
 
declare @i int, @si varchar(10), @startdate date
set @i = 1
while @i <= 10
begin
 set @si = right('0'+CONVERT(varchar(10), @i),2)
 insert into Customer
 ( CustomerId, CustomerName, DateOfBirth, Town, TelephoneNo, DrivingLicenceNo, Occupation)
 values
 ( @i, 'Customer'+@si, DATEADD(d,@i-1,'2000-01-01'), 'Town'+@si, 
       'Phone'+@si, 'Licence'+@si, 'Occupation'+@si)
 set @i = @i + 1
end
go
 
-- Create customer table
if exists (select * from sys.tables where name = 'CustomerUpdate')
drop table CustomerUpdate
go
 
create table CustomerUpdate
( 
CustomerId int not null primary key,
CustomerName varchar(30), DateOfBirth date, Town varchar(50),
TelephoneNo varchar(30), DrivingLicenceNo varchar(30), Occupation varchar(30)
)
go
 
-- Populate Customer Destination
 
declare @i int, @si varchar(10), @startdate date
set @i = 1
while @i <= 5
begin
 set @si = right('0'+CONVERT(varchar(10), @i),2)
 insert into CustomerUpdate
 ( CustomerId, CustomerName, DateOfBirth, Town, TelephoneNo, DrivingLicenceNo, Occupation)
 values
 ( @i, 'Customer'+@si, DATEADD(d,@i-1,'2000-01-01'), 'Town'+@si, 'Phone'+@si, 'Licence'+@si, 'Occupation'+@si)
 set @i = @i + 1
end
go

This script will create two tables: Customer and CustomerUpdate. Customer is our source table which contains 10 record and CustomerUpdate is our dimension table which contains 5 record and need to update if Customer table is update and insert if new record found in Customer table.

Customer

CustomerUpdate

Then open SQL Server Business Intelligence Development Studio.

Then go to File->New->Project and select Integration Service Project.

Select "Execute Task" from "Control Flow Items" and Drag it on "Control Flow" tab. Then double click it.

Click Connection for new connection or select from existing connection.

Click New button to create new Data Connection or select from left tab.

Select "Server Name", "Authentication", and "Database" which will be "Test" for this example. Click Test Connection for checking then click OK and then again click OK.

Now click SQLStatement for writing SQL.

Write SQL:

delete CustomerUpdate
from
(
select * from Customer
EXCEPT
select * from CustomerUpdate
)
src INNER JOIN CustomerUpdate dest ON src.CustomerId = dest.CustomerId

Click Ok and rename the "Execute SQL Task" to "For Update".

Select "Execute Task" from "Control Flow Items" and Drag it on "Control Flow" tab. Rename it "To Insert" and then double click it.

Select Previously created connection.

Now click SQLStatement for writing SQL.

Write SQL:

insert into CustomerUpdate
select a.*
from
(
select * from Customer
EXCEPT
select * from CustomerUpdate
) a

Click OK. Now we are ready to execute the SSIS. If you execute the package with debugging (press F5), the package should succeed and appear as shown here:

To check what is happening here:

select * from CustomerUpdate

Our destination table CustomerUpdate have got all the new record which was not available previously.

Now update some data in Customer table.

UPDATE [Test].[dbo].[Customer]
   SET [CustomerName] = 'aaa'
      ,[DateOfBirth] = '2012-01-01'
      ,[Town] = 'aaaaaa'
 WHERE [CustomerId] =1
GO

UPDATE [Test].[dbo].[Customer]
   SET [CustomerName] = 'bbb'
      ,[DateOfBirth] = '2012-01-01'
      ,[Town] = 'bbbbbb'
 WHERE [CustomerId] =2

Customer

CustomerUpdate

If you execute the package with debugging (press F5), the package should succeed and appear as shown here:

To check what is happening here:

select * from CustomerUpdate

Now our destination table CustomerUpdate have got all the update record which was not available previously.

Conclusion

Please check my other tips on loading data in a dimension table. I hope this might be helpful to you!

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

mparvez
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0
 
An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.
 
Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.
 
Certification
 
70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker
 
Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez
Follow on   Google+

Comments and Discussions

 
SuggestionWonderful PinmemberRamesh Vaasu6-Feb-14 2:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 15 Jan 2013
Article Copyright 2013 by mparvez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid