ETL Process for the Campaign Data Import DTS Task

  • Article

The Campaign Data Import DTS task imports campaign information such as advertisers, campaigns, and campaign data. The Campaign Data Import task should be run before running reports that summarize campaign data.

The following tables list the columns extracted from the source tables in the Site_Commerce database, the transformation made, and the tables to which the data is saved in the Data Warehouse.

Source Columns from the ad_item table of the Site_Commerce database Transformation Target columns from the AdItem table in the Data Warehouse 
i_aditem_id
 Copy Column 
AdItemID
 
u_aditem_customer_order_num
 Copy Column 
AdItemCustomerOrderNum
 
i_aditem_days_of_week_mask
 Copy Column 
AdItemDaysOfWeekMask
 
i_aditem_events_scheduled
 Copy Column 
AdItemEventsScheduled
 
i_aditem_tiem_of_day_end
 Copy Column 
AdItemTimeOfDayEnd
 
i_aditem_time_of_day_start
 Copy Column 
AdItemTiemOfDayStart
 
i_aditem_type
 Copy Column 
AdItemType
 
i_aditem_weight
 Copy Column 
AdItemWeight
 
i_campitem_id
 Copy Column 
CampaignItemID
 
i_industry_id
 Copy Column 
IndustryCodeID
 
i_aditem_id
Copy Column 
AdItemNum
Source columns from the campaign table of the Site_Commerce database Transformation Target columns from the Campaign table in the Data Warehouse 
b_camp_active
 Copy Column 
CampActive
 
dt_campaign_exported
 Copy Column 
CampaignExported
 
dt_campaign_modified
 Copy Column 
CampaignModified
 
u_campaign_modified_by
 Copy Column 
CampaignModifiedBy
 
dt_camp_archived
 Copy Column 
CampArchived
 
dt_camp_end
 Copy Column 
CampEnd
 
i_camp_events_sched
 Copy Column 
CampEventsSched
 
b_camp_level_goal
 Copy Column 
CampLevelGoal
 
u_camp_name
 Copy Column 
CampName
 
i_camp_id
 Copy Column 
CampNum
 
dt_camp_start
 Copy Column 
CampStart
 
u_camp_comments
 Copy Column 
CustomerComments
 
i_customer_id
 Copy Column 
CustomerID
 
i_event_type_id
 Copy Column 
EventTypeID
Source columns from the campaign_item table of the Site_Commerce database Transformation Target columns from the CampaignItem table in the Data Warehouse 
u_campitem_active
 Copy Column 
Active
 
dt_campitem_archived
 Copy Column 
ArchivedDate
 
i_camp_id
 Copy Column 
CampItemNum
 
u_campitem_comments
 Copy Column 
Comments
 
dt_campitem_end
 Copy Column 
EndDate
 
dt_campitem_exported
 Copy Column 
ExportedDate
 
i_campitem_exposure_limit
 Copy Column 
ExposureLimit
 
u_campitem_modified_by
 Copy Column 
ModifiedBy
 
dt_campitem_modified
 Copy Column 
ModifiedDate
 
u_campitem_name
 Copy Column 
Name
 
dt_campitem_start
 Copy Column 
StartDate
 
i_campitem_id
 Copy Column 
CampaignID
 
guid_campitem_type
 Copy Column 
CampaignItemTypesID
 
i_creative_id
 Copy Column 
CreativeID
Source columns from the campaign_item_types table of the Site_Commerce database Transformation Target columns from the CampaignItemTypes table in the Data Warehouse 
u_campitem_type_name
 Copy Column 
CampItemTypeName
 
u_campitem_type_table
 Copy Column 
CampItemTypeTable
 
guid_campitem_type
 Copy Column 
GUIDCampItemType
Source columns from the Creative table of the Site_Commerce database Transformation Target columns from the Creative table in the Data Warehouse 
i_creative_id
 Copy Column 
CreativeNum
 
dt_creative_received
 Copy Column 
CreativeReceived
 
i_creative_size_id
 Copy Column 
CreativeSizeID
 
i_creative_type_id
 Copy Column 
CreativeTypeID
 
i_customer_id
 Copy Column 
CustomerID
Source columns from the creative_size table of the Site_Commerce database Transformation Target columns from the CreativeSize Report table in the Data Warehouse 
i_creative_size_id
 Copy Column 
CreativeSizeNum
 
i_size_height
 Copy Column 
SizeHeight
 
u_size_name
 Copy Column 
SizeName
 
u_size_tag
 Copy Column 
SizeTag
 
i_size_width
 Copy Column 
SizeWidth
Source columns from the creative_type table of the Site_Commerce database Transformation Target creativetypes from the Report table in the Data Warehouse 
u_ct_name
 Copy Column 
CreativeTypeName
 
i_creative_type_id
 Copy Column 
CreativeTypeNum
 
text_ct_template
 Copy Column 
CreativeTypeTemplate
Source columns from the creative_type_xref table of the Site_Commerce database Transformation Target columns from the CreativeTypeXref table in the Data Warehouse 
guid_campitem_type
 Copy Column 
CampaignItemTypesID
 
i_creative_type_id
 Copy Column 
CreativeTypeID
Source columns from the Customer table of the Site_Commerce database Transformation Target columns from the Customer table in the Data Warehouse 
u_customer_address
 Copy Column Address 
dt_customer_archived
 Copy Column Archived 
u_customer_comments
 Copy Column Comments 
u_customer_contact
 Copy Column Contact 
i_customer_id
 Copy Column CustomerNum 
i_customer_type
 Copy Column CustomerTypeNum 
u_customer_email
 Copy Column Email 
dt_customer_exported
 Copy Column Exported 
u_customer_fax
 Copy Column Fax 
dt_customer_modified
 Copy Column Modified 
u_customer_modifiedby
 Copy Column ModifiedBy 
u_customer_name
 Copy Column Name 
u_customer_password
 Copy Column PassWord 
u_customer_phone
 Copy Column Phone 
u_customer_url
 Copy Column URL 
u_customer_username
 Copy Column UserName 
i_industry_id
 Copy Column IndustryCodeID
Source columns from the dm_item table of the Site_Commerce database Transformation Target columns from the DmItem table in the Data Warehouse 
u_dmitem_attachment
 Copy Column 
DmItemAttachment
 
l_dmitem_defformat
 Copy Column 
DmItemDefformat
 
u_dmitem_desc
 Copy Column 
DmItemDesc
 
u_dmitem_from
 Copy Column 
DmItemFrom
 
i_dmitem_id
 Copy Column 
DmItemNum
 
b_dmitem_personalized
 Copy Column 
DmItemPersonalized
 
u_dmitem_replyto
 Copy Column 
DmItemReplyTo
 
u_dmitem_scheduleid
 Copy Column 
DmItmeScheduleId
 
u_dmitem_source
 Copy Column 
DmItemSource
 
u_dmitem_subj
 Copy Column 
DmItemSubj
 
i_dmitem_userflags
 Copy Column 
DmItemUserFlags
 
g_dmitem_listid
 Copy Column 
GUIDDmItemListId
 
g_dmitem_optout
 Copy Column 
GUIDDmItemOptout
 
g_dmitem_testlistid
 Copy Column 
GUIDDmItemTestListId
 
i_campitem_id
 Copy Column 
CampaignItemID
Source columns from the event_type table of the Site_Commerce database Transformation Target columns from the EventType table in the Data Warehouse 
u_event_description
 Copy Column 
EventDescription
 
u_event_name
 Copy Column 
EventName
 
i_event_type_id
 Copy Column 
EventTypeID
Source columns from the industry_code table of the Site_Commerce database Transformation Target columns from the IndustryCode table in the Data Warehouse 
u_industry_code
 Copy Column 
IndustryCode
 
i_industry_id
 Copy Column 
IndustryNum
Source columns from the order_discount table of the Site_Commerce database Transformation Target columns from the OrderDiscount table in the Data Warehouse 
i_disc_award_expr
 Copy Column 
DiscAwardExpr
 
i_disc_award_max
 Copy Column 
DiscAwardMax
 
b_disc_click_required
 Copy Column 
DiscClickRequired
 
i_disc_cond_basis
 Copy Column 
DiscCondBasis
 
i_disc_cond_expr
 Copy Column 
DiscCondExpr
 
mny_disc_cond_min
 Copy Column 
DiscCondMin
 
u_disc_description
 Copy Column 
DiscDescription
 
b_disc_disjoin_cond_award
 Copy Column 
DiscDisjointCondAward
 
b_disc_expressions_used_for_display
 Copy Column 
DiscExprUsedForDisplay
 
i_disc_limit
 Copy Column 
DiscLimit
 
i_disc_id
 Copy Column 
DiscNum
 
i_disc_offer_type
 Copy Column 
DiscOfferType
 
mny_disc_offer_value
 Copy Column 
DiscOfferValue
 
b_disc_order_level_discount
 Copy Column 
DiscOrderLevelDiscount
 
i_disc_qty_cond_min
 Copy Column 
DiscQtyCondMin
 
i_disc_rank
 Copy Column 
DiscRank
 
u_disc_special_offer_type
 Copy Column 
DiscSpecialOfferType
 
i_disc_template
 Copy Column 
DiscTemplate
 
i_campitem_id
 Copy Column 
CampaignItemID
Source columns from the page_group table of the Site_Commerce database Transformation Target columns from the PageGroup table in the Data Warehouse 
u_pg_description
 Copy Column 
PgDescription
 
i_pg_id
 Copy Column 
PgNum
 
u_pg_tag
 Copy Column 
PgTag
Source columns from the page_group_xref table of the Site_Commerce database Transformation Target columns from the PageGroupXref table in the Data Warehouse 
i_campitem_id
 Copy Column 
CampaignItemID
 
i_pg_id
 Copy Column 
PageGroupID
Source columns from the target table of the Site_Commerce database Transformation Target columns from the Target table in the Data Warehouse 
i_target_action
 Copy Column 
TargetAction
 
i_target_expr_id
 Copy Column 
TargetExprId
 
i_target_id
 Copy Column 
TargetNum
 
i_targroup_id
 Copy Column 
TargetGroupID
Source columns from the target_group table of the Site_Commerce database Transformation Target columns from the TargetGroup table in the Data Warehouse 
u_targroup_name
 Copy Column 
TargetGroupName
 
i_targroup_id
 Copy Column 
TargetGroupNum
Source columns from the target_group_xref table of the Site_Commerce database Transformation Target columns from the TargetGroupXref table in the Data Warehouse 
i_campitem_id
 Copy Column 
CampaignItemID
 
i_targroup_id
 Copy Column 
TargetGroupID

See Also

Running the Campaign Data Import DTS Task

Scripting the Campaign Data Import DTS Task

Workflow for Running DTS Tasks

Extending the Data Warehouse

Data Warehouse Schema

Copyright © 2005 Microsoft Corporation.
All rights reserved.