In this recipe, we will build the ETL job to populate two dimension tables in the AdventureWorks_DWH
database, DimGeography
and DimSalesTerritory
, with the data from the operational database AdventureWorks_OLTP
.
For this recipe, you will have to create new job. Also, create two new schemas in the STAGE
database: Extract
and Transform
. To do this, open the SQL Server Management Studio, expand Databases | STAGE | Security | Schemas, right-click on the Schemas folder, and choose the New Schema… option from the context menu. Specify your administrator user account as a schema owner.
In the first step, we will create extraction processes using these steps:
Open the job context and create the
WF_extract
workflow.Open the
WF_extract
workflow in the workspace and create four workflows: each for every source table we extract from the OLTP database:WF_Extract_SalesTerritory
,WF_Extract_Address
,WF_Extract_StateProvince
,WF_Extract_CountryRegion...