In this tutorial, you will start the process of building the DimCustomer SSIS package that will
handle the ETL process from the AdventureWorks2008 database to the AdventureWorks-
DW2008 database.
1. If necessary, start SQL Server Business Intelligence Development Studio (BIDS), open
the project SSIS Project you created in tutorial, “Creating SSIS Packages
and Data Sources,” and then open the empty DimCustomer package.
2. From the toolbox, drag two Execute SQL Tasks onto the control flow workspace and
then drag one Data Flow Task onto the workspace.
3. Next, connect the first Execute SQL Task to the Data Flow Task by dragging the green
precedence constraint from the Execute SQL Task onto the Data Flow Task. Then connect
the green precedence constraint from the Data Flow Task to the second Execute
SQL Task.
4. Rename the first Execute SQL Task to Truncate Update Table, and rename the second
Execute SQL Task to Batch Updates.
Your control flow for the DimCustomer package should contain an Execute SQL Task,
followed by a Data Flow Task, followed by another Execute SQL Task.
5. Before editing the tasks in SSIS, open SSMS, connect to the Database Engine, and create
a new query against the AdventureWorksDW2008 database. Execute the following
code.
USE AdventureWorksDW2008
GO
CREATE TABLE [dbo].[stgDimCustomerUpdates](
[CustomerAlternateKey] [nvarchar](15) NULL,
[AddressLine1] [nvarchar](60) NULL,
[AddressLine2] [nvarchar](60) NULL,
[BirthDate] [datetime] NULL,
[CommuteDistance] [nvarchar](15) NULL,
[DateFirstPurchase] [datetime] NULL,
[EmailAddress] [nvarchar](50) NULL,
[EnglishEducation] [nvarchar](40) NULL,
[EnglishOccupation] [nvarchar](100) NULL,
[FirstName] [nvarchar](50) NULL,
[Gender] [nvarchar](1) NULL,
[GeographyKey] [int] NULL,
[HouseOwnerFlag] [nvarchar](1) NULL,
[LastName] [nvarchar](50) NULL,
[MaritalStatus] [nvarchar](1) NULL,
[MiddleName] [nvarchar](50) NULL,
[NumberCarsOwned] [tinyint] NULL,
[NumberChildrenAtHome] [tinyint] NULL,
[Phone] [nvarchar](25) NULL,
[Suffix] [nvarchar](10) NULL,
[Title] [nvarchar](8) NULL,
[TotalChildren] [tinyint] NULL,
[YearlyIncome] [nvarchar](100) NULL) ON [PRIMARY]
6. After you have successfully created the table, switch back to the DimCustomer SSIS
package and edit the Execute SQL Task named Truncate Update Table.
7. In the Execute SQL Task Editor dialog box, set the Connection property to Adventure-
WorksDW2008, and then enter the following SQL code in the SQLStatement property
before clicking OK to save it:
TRUNCATE TABLE dbo.stgDimCustomerUpdates
8. Edit the last Execute SQL Task, named Batch Updates, and set the Connection property
to AdventureWorksDW2008.
9. In the SQLStatement property, enter the following UPDATE statement.
UPDATE dbo.DimCustomer
SET AddressLine1 = stgDimCustomerUpdates.AddressLine1
, AddressLine2 = stgDimCustomerUpdates.AddressLine2
, BirthDate = stgDimCustomerUpdates.BirthDate
, CommuteDistance = stgDimCustomerUpdates.CommuteDistance
, DateFirstPurchase = stgDimCustomerUpdates.DateFirstPurchase
, EmailAddress = stgDimCustomerUpdates.EmailAddress
, EnglishEducation = stgDimCustomerUpdates.EnglishEducation
, EnglishOccupation = stgDimCustomerUpdates.EnglishOccupation
, FirstName = stgDimCustomerUpdates.FirstName
, Gender = stgDimCustomerUpdates.Gender
, GeographyKey = stgDimCustomerUpdates.GeographyKey
, HouseOwnerFlag = stgDimCustomerUpdates.HouseOwnerFlag
, LastName = stgDimCustomerUpdates.LastName
, MaritalStatus = stgDimCustomerUpdates.MaritalStatus
, MiddleName = stgDimCustomerUpdates.MiddleName
, NumberCarsOwned = stgDimCustomerUpdates.NumberCarsOwned
, NumberChildrenAtHome = stgDimCustomerUpdates.NumberChildrenAtHome
, Phone = stgDimCustomerUpdates.Phone
, Suffix = stgDimCustomerUpdates.Suffix
, Title = stgDimCustomerUpdates.Title
, TotalChildren = stgDimCustomerUpdates.TotalChildren
FROM dbo.DimCustomer DimCustomer
INNER JOIN dbo.stgDimCustomerUpdates
ON DimCustomer.CustomerAlternateKey
= stgDimCustomerUpdates.CustomerAlternateKey
10. Click OK in the Execute SQL Task Editor dialog box, and then save the package. In the
next lesson, you will complete the data flow portion of this package and then test the
execution.
References
MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)