I'm trying to investigate an issue where a date field, PolicyCancelDt, of type datetime from an extract table is changing to NULL after loading into a dimension table, where the corresponding field is of type int. There is a conversion in the query for the extract:
po.sPolicyID AS PolicyAgreementNumber
, mv.[Market Vertical] as MarketVerticalName
, c.sCustomerID + p.sProductID as CombinedClientProductID
, CONVERT(Int,Convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
, CONVERT(Int,Convert(varchar(8), po.PolicyEfftvDt, 112)) as PolicyEfftvDt
, CONVERT(Int,Convert(varchar(8), po.PolicyExpirDt, 112)) as PolicyExpirDt
, CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as PolicyCancelDt
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
WHERE jw.sProductID is not null OR p.sProductID = '9095'
ORDER BY PolicyAgreementNumber
I'm not sure why this is happening for some records. There's a slowly changing dimension data flow as part of this package, but this date field has no part in it. Could someone please advise on why this might be happening?