我有两个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实施Datawarehouses \ 11-PackageLogic \ DimDate.dtsx”开始。 错误：Load DimDate中的0xC002F210，执行SQL任务：执行查询“ “因以下错误而失败：”多步OLE DB操作生成错误。 检查每个OLE DB状态值（如果可用）。 没有工作。“。可能的失败原因：查询问题，”ResultSet“属性设置不正确，参数设置不正确或连接未正确建立。任务失败：加载DimDate SSIS包”C：\ Users \ raihan \ documents \ visual studio 2013 \ Projects \ 70-463实现Datawarehouses \ 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) GO我在SQL Server中的DimDate表结构是：
我已将问题视频上传到Youtube 这是链接
I have two parameters of DimDate (both of them are string):
My parameters mapping are (both of them are NVARCHAR):
and my query in DimDate SQL task:
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) GOYou can see that I have two ? marks in my query, where BeginGenerateDate and EndGenerateDate parameter values should be inserted dynamically. The first one should cast from a string to a DateTime and the second one should compare with EndDate. But when I execute this task it always throws an error in the output which is
SSIS package "C:\Users\raihan\documents\visual studio 2013\Projects\70-463 Implementing Datawarehouses\11-PackageLogic\DimDate.dtsx" starting. Error: 0xC002F210 at Load DimDate, Execute SQL Task: Executing the query " " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Load DimDate SSIS package "C:\Users\raihan\documents\visual studio 2013\Projects\70-463 Implementing Datawarehouses\11-PackageLogic\DimDate.dtsx" finished: Success.
The following SQL query will work but it does not fulfill my requirements as you can see I can't insert my parameter
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) GOMy DimDate table structure in SQL Server is:
I have uploaded my problem video into Youtube Here is the Link
最满意答案
我可以发现的一个问题是，您的变量值似乎不包含值周围的任何引号 - Integration Services将把这些文字值替换为您的查询而没有引号您将最终得到的是：
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语句以将它们附加到变量值。
I have found a solution but still don't know how this is working :D...... Before Using CTE, i have to convert the parameters to DateTime and Date respectively.
DECLARE @startDate DateTime; SET @startDate = CAST (? AS DateTime); DECLARE @endDate Date; SET @endDate = CAST (? AS Date);Following Code is Working Fine
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)更多推荐
发布评论