SSIS SQL任务查询在转换参数时创建错误(字符串到日期时间)
SSIS SQL任务查询在转换参数时创建错误(字符串到日期时间)
提问于 2015-11-22 16:32:08
我有两个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 这是链接
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提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33857205
复制相关文章
相似问题
腾讯云开发者
