Stage 5 fails to migrate contact and response history records prior to year 2000

Problem or error: Stage 5 fails to migrate contact and response history records dated prior to January 1, 2000 and displays an integrity constraints violated (parent key not found) error.

Solution: By default, the UA_Calendar table contains DateID records beginning with 20000101 (corresponding to January 1, 2000). Contact and response history records prior to January 1, 2000 fail to migrate because their ContactDateTime or ResponseDateTime values refer to date IDs that do not exist in the UA_Calendar table.

To work around this problem, follow these steps to populate the target system's UA_Calendar table with the required date IDs.

  1. Run the following queries on your source system's database to determine the earliest contact and response history records:

    select min(ContactDate) from UA_ContactHistory

    select min(ResponseDate) from UA_ResponseHistory

  2. Populate the target system's UA_Calendar table with date IDs for the desired range of dates. The following example for SQL Server inserts records for January 1, 1999 to December 31, 1999. Replace the values in bold to specify the beginning and end dates, respectively.
Note: The following example code is from the ac_populate_tables_sqlsvr.sql script in the ddl directory under your Unica Campaign installation. If your database is DB2® or Oracle, use the code from the DDL script for your database type (ac_populate_tables_db2.sql for DB2® or ac_populate_tables_ora.sql for Oracle).
declare @date_v datetime
set @date_v = '01/01/1999'
set datefirst 7
set dateformat mdy
while @date_v <= '12/31/1999' 
begin
	insert into UA_Calendar
	(Year, FiscalYear, Quarter, FiscalQuarter, 
	Month, 
	WeekOfYear, 
	WeekOfMonth, 
	DayOfYear, DayOfMonth, DayOfWeek, 
	ActualDate, 
	FirstDayOfWeek, 
	LastDayOfWeek, 
	DateID) values 
	(DATEPART(yyyy, @date_v), DATEPART(yyyy, @date_v), DATEPART(q,
@date_v),DATEPART(q, @date_v),
	DATEPART(mm, @date_v),
	DATEPART(ww, @date_v),
	DATEDIFF(dd, DATEADD(dd,-1,DATEADD(ww, DATEDIFF(ww,0,DATEADD(dd,-
(DAY(@date_v)-1),@date_v)), 0)), @date_v)/7 + 1,
	DATEPART(dy, @date_v), DATEPART(dd, @date_v), DATEPART(dw, @date_v),
	@date_v, 
	DATEADD(dd, -1, DATEADD(wk, DATEDIFF(wk,0,@date_v), 0)),
	DATEADD(dd, 5, DATEADD(wk, DATEDIFF(wk,0,@date_v), 0)),
	CAST (CONVERT (varchar(20), @date_v, 112) AS BIGINT))
	set @date_v = DATEADD(dd, 1, @date_v)

end