SSIS SQL任务查询在转换参数时创建错误(字符串到日期时间)
我有两个DimDate参数(都是字符串)：
我的参数映射是(它们都是NVARCHAR)：
以及我在DimDate SQL任务中的查询：
WITH DimDateCTE AS
(
SELECT CAST ( ? AS DateTime) FullDate
UNION ALL
SELECT
FullDate + 1
FROM
DimDateCTE
WHERE
FullDate + 1 <= ?
)
INSERT INTO [dbo].[DimDate]
SELECT
CAST(CONVERT(CHAR(8), CAST(FullDate AS DATETIME), 112) AS INT) AS DateKey,
FullDate AS FullDateAlternateKey,
DATEPART(dw, FullDate) AS DayNumberOfWeek,
DATENAME(dw, FullDate) AS EnglishDayNameOfWeek,
'' AS SpanishDayNameOfWeek,
'' AS FrenchDayNameOfWeek,
DAY(FullDate) AS DayNumberOfMonth,
DATEPART(dy, FullDate) AS DayNumberOfYear,
DATEPART(wk, FullDate) AS WeekNumberOfYear,
DATENAME(mm, FullDate) AS EnglishMonthName,
'' AS SpanishMonthName,
'' AS FrenchMonthName,
MONTH(FullDate) AS MonthNumberOfYear,
DATEPART(qq, FullDate) AS CalenderQuarter,
YEAR(FullDate) AS CalenderYear,
CASE WHEN MONTH(FullDate) >= 1 AND MONTH(FullDate) <=6 THEN 1 ELSE 2 END AS CalendarSemester,
DATEPART(quarter, FullDate) AS FiscalDate,
YEAR(FullDate) AS FiscalYear,
(MONTH(FullDate+3)/4) AS FiscalSemester
FROM
DimDateCTE
OPTION(MAXRECURSION 0)
GO您可以看到，我的查询中有两个 ?标记，其中应该动态插入BeginGenerateDate和EndGenerateDate参数值。第一个应该从一个字符串转换为一个DateTime，第二个应该与EndDate进行比较。但是，当我执行此任务时，它总是在输出中抛出一个错误，即
SSIS包"C:\Users\raihan\documents\visual studio 2013\Projects\70-463实现Datawarehouse\11-PackageLogic\DimDate.dtsx“启动。 错误: 0xC002F210在加载DimDate时，执行SQL :执行查询“ “失败并有以下错误：”多步OLE DB操作生成错误。如果可用，请检查每个OLE DB状态值。没有完成任何工作。“。可能的失败原因:查询出现问题、"ResultSet”属性未正确设置、参数未正确设置或连接未正确建立。任务失败:加载DimDate SSIS包“C：\DimDate\raihan\documents\visual studio 2013\visual studio 2013\Projects\70-463实现Datawarehouse\11-PackageLogic\DimDate.dtsx”已完成:成功。
下面的SQL查询将工作，但它不能满足我的要求，因为您可以看到，我无法插入参数
WITH DimDateCTE AS
(
SELECT CAST ('2000-01-01' AS DateTime) FullDate
UNION ALL
SELECT
FullDate + 1
FROM
DimDateCTE
WHERE
FullDate + 1 <= '2020-12-31'
)
INSERT INTO [dbo].[DimDate]
SELECT
CAST(CONVERT(CHAR(8), CAST(FullDate AS DATETIME), 112) AS INT) AS DateKey,
FullDate AS FullDateAlternateKey,
DATEPART(dw, FullDate) AS DayNumberOfWeek,
DATENAME(dw, FullDate) AS EnglishDayNameOfWeek,
'' AS SpanishDayNameOfWeek,
'' AS FrenchDayNameOfWeek,
DAY(FullDate) AS DayNumberOfMonth,
DATEPART(dy, FullDate) AS DayNumberOfYear,
DATEPART(wk, FullDate) AS WeekNumberOfYear,
DATENAME(mm, FullDate) AS EnglishMonthName,
'' AS SpanishMonthName,
'' AS FrenchMonthName,
MONTH(FullDate) AS MonthNumberOfYear,
DATEPART(qq, FullDate) AS CalenderQuarter,
YEAR(FullDate) AS CalenderYear,
CASE WHEN MONTH(FullDate) >= 1 AND MONTH(FullDate) <=6 THEN 1 ELSE 2 END AS CalendarSemester,
DATEPART(quarter, FullDate) AS FiscalDate,
YEAR(FullDate) AS FiscalYear,
(MONTH(FullDate+3)/4) AS FiscalSemester
FROM
DimDateCTE
OPTION(MAXRECURSION 0)
GOServer中的DimDate表结构是：
我已经把我的问题视频上传到Youtube 这是链接
回答 2
Stack Overflow用户
发布于 2015-11-24 09:04:37
我已经找到了解决办法，但还是不知道这是怎么回事:D.在使用CTE之前，我必须将参数分别转换为DateTime和Date。
DECLARE @startDate DateTime;
SET @startDate = CAST (? AS DateTime);
DECLARE @endDate Date;
SET @endDate = CAST (? AS Date);以下代码运行良好
DECLARE @startDate DateTime;
SET @startDate = CAST (? AS DateTime);
DECLARE @endDate Date;
SET @endDate = CAST (? AS Date);
WITH DimDateCTE AS
(
SELECT @startDate AS FullDate
UNION ALL
SELECT
FullDate + 1
FROM
DimDateCTE
WHERE
FullDate + 1 <= @endDate
)
INSERT INTO [dbo].[DimDate]
SELECT
CAST(CONVERT(CHAR(8), CAST(FullDate AS DATETIME), 112) AS INT) AS DateKey,
FullDate AS FullDateAlternateKey,
DATEPART(dw, FullDate) AS DayNumberOfWeek,
DATENAME(dw, FullDate) AS EnglishDayNameOfWeek,
'' AS SpanishDayNameOfWeek,
'' AS FrenchDayNameOfWeek,
DAY(FullDate) AS DayNumberOfMonth,
DATEPART(dy, FullDate) AS DayNumberOfYear,
DATEPART(wk, FullDate) AS WeekNumberOfYear,
DATENAME(mm, FullDate) AS EnglishMonthName,
'' AS SpanishMonthName,
'' AS FrenchMonthName,
MONTH(FullDate) AS MonthNumberOfYear,
DATEPART(qq, FullDate) AS CalenderQuarter,
YEAR(FullDate) AS CalenderYear,
CASE WHEN MONTH(FullDate) >= 1 AND MONTH(FullDate) <=6 THEN 1 ELSE 2 END AS CalendarSemester,
DATEPART(quarter, FullDate) AS FiscalDate,
YEAR(FullDate) AS FiscalYear,
(MONTH(FullDate+3)/4) AS FiscalSemester
FROM
DimDateCTE
OPTION(MAXRECURSION 0)Stack Overflow用户
发布于 2015-11-22 21:12:55
我能注意到的一个问题是，变量值似乎不包含值周围的任何引号- Integration将在查询中替换这些文字值，而没有引号，您将得到的结果是：
SELECT CAST ( 1/1/2000 AS DateTime) FullDate
UNION ALL
SELECT
FullDate + 1
FROM
DimDateCTE
WHERE
FullDate + 1 <= 12/31/2020这将出错，因为日期值需要在单引号中处理为字符串。
您可以将引号放在变量值中，例如将'1/1/2000‘值(包括引号)存储在变量中，也可以编辑SQL语句将它们附加到变量值中。
https://stackoverflow.com/questions/33857205
复制相似问题
腾讯云开发者
