<<<June 2018>>>
22     12

Display as : cloud | list


Go Back

Developing SSIS Packages - Modify the DimCustomer ETL Package Control Flow


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



USE AdventureWorksDW2008


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




MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)