Visual Studio 2017 ETL中INT字段被识别为datetime问题求助

看起来你的问题出在Visual Studio的类型推断逻辑上——虽然你的子查询确实返回的是timeKey这个INT类型字段，但工具可能因为子查询里关联的是datetime类型的日期字段（比如r.reservationDate），错误地把这些字段的类型推断成了datetime，导致ETL过程中出现类型不匹配的报错。下面给你几个可行的解决方案：

方案1：显式转换子查询结果为INT类型 #

在每个子查询的外层加上CAST或CONVERT，强制指定返回类型为INT，彻底避免工具的类型推断错误。修改后的查询示例：

SELECT 
    e.eventName, 
    e.eventType, 
    e.numberOfPersons, 
    CAST((SELECT timeKey FROM StarSchema.dbo.timeDim WHERE (r.reservationDate = [DATE])) AS INT) AS reservationDate, 
    CAST((SELECT timeKey FROM StarSchema.dbo.timeDim AS timeDim_2 WHERE (e.eventStartDate = [DATE])) AS INT) AS eventStartDate, 
    CAST((SELECT timeKey FROM StarSchema.dbo.timeDim AS timeDim_1 WHERE (e.eventEndDate = [DATE])) AS INT) AS eventEndDate, 
    contact.name, 
    customer.company, 
    invoices.price, 
    invoices.invoiceId 
FROM events AS e 
INNER JOIN reservation AS r ON e.reservationId = r.reservationId 
INNER JOIN customer ON e.customerId = customer.customerId 
INNER JOIN contact ON customer.contactId = contact.contactId 
INNER JOIN invoices ON e.invoiceId = invoices.invoiceId

方案2：改用JOIN替代子查询（更推荐） #

子查询的写法容易让元数据解析工具混淆，换成JOIN的方式直接关联时间维度表，这样timeKey的INT类型会被工具明确识别，同时查询的可读性和性能也会更好。修改后的查询示例：

SELECT 
    e.eventName, 
    e.eventType, 
    e.numberOfPersons, 
    td_res.timeKey AS reservationDate,
    td_start.timeKey AS eventStartDate,
    td_end.timeKey AS eventEndDate,
    contact.name, 
    customer.company, 
    invoices.price, 
    invoices.invoiceId 
FROM events AS e 
INNER JOIN reservation AS r ON e.reservationId = r.reservationId
-- 关联时间维度表获取对应timeKey
INNER JOIN StarSchema.dbo.timeDim td_res ON r.reservationDate = td_res.[DATE]
INNER JOIN StarSchema.dbo.timeDim td_start ON e.eventStartDate = td_start.[DATE]
INNER JOIN StarSchema.dbo.timeDim td_end ON e.eventEndDate = td_end.[DATE]
INNER JOIN customer ON e.customerId = customer.customerId 
INNER JOIN contact ON customer.contactId = contact.contactId 
INNER JOIN invoices ON e.invoiceId = invoices.invoiceId

方案3：创建视图时强制指定字段类型 #

如果需要基于查询创建视图，可以在视图定义中明确指定这些字段的类型，确保ETL工具读取视图元数据时获取正确的类型。示例：

CREATE VIEW EventFactView
AS
SELECT 
    e.eventName, 
    e.eventType, 
    e.numberOfPersons, 
    reservationDate = CONVERT(INT, (SELECT timeKey FROM StarSchema.dbo.timeDim WHERE r.reservationDate = [DATE])),
    eventStartDate = CONVERT(INT, (SELECT timeKey FROM StarSchema.dbo.timeDim WHERE e.eventStartDate = [DATE])),
    eventEndDate = CONVERT(INT, (SELECT timeKey FROM StarSchema.dbo.timeDim WHERE e.eventEndDate = [DATE])),
    contact.name, 
    customer.company, 
    invoices.price, 
    invoices.invoiceId 
FROM events AS e 
INNER JOIN reservation AS r ON e.reservationId = r.reservationId 
INNER JOIN customer ON e.customerId = customer.customerId 
INNER JOIN contact ON customer.contactId = contact.contactId 
INNER JOIN invoices ON e.invoiceId = invoices.invoiceId
GO

这些方案都能让reservationDate、eventStartDate、eventEndDate字段被正确识别为INT类型，从而顺利关联时间维度表，解决ETL中的类型转换报错问题。

内容的提问来源于stack exchange，提问作者J. Meijerink