问题描述
我得到了错误:
[执行SQL任务]错误:执行查询"DECLARE? datetime = '01 / 01 / 2000'DECLARE? dateti ..."失败并出现以下错误:"没有为一个或多个必需参数指定值。"。可能的失败原因:
查询出现问题,"ResultSet"属性设置不正确,参数设置不正确或连接设置不正确。
[Execute SQL Task] Error: Executing the query "DECLARE ? datetime = '01/01/2000' DECLARE ? dateti..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
它在SQL中运行完美,但在SSiS中运行不正常,请帮帮我!
It works perfect in SQL , but not in SSiS , Please helping me out !
声明@StartDate datetime = '01 / 01/2000'
声明@EndDate datetime = '01 / 01/2010'
- 使用while循环将日期添加到tabl e
声明@DateInProcess datetime
设置@DateInProcess = @StartDate
而@DateInProcess< = @EndDate
开始
- 在此日期的日期维度表中添加一行
Insert into DimDates
值(
convert(char(8),@ DateInProcess,112) - [DateKey]
,DateName(工作日,@ DateInProcess)+','+ Convert(nvarchar(100),@ DateInProcess,112) - [USADateName]
,左(Cast(转换(nvarchar(100),@ DateInProcess,112)为int),6) - [MonthKey]
,DateName(MONTH,@ DateInProcess)+','+ Cast(Year(@DateInProcess)as nVarchar(100)) - [MonthName]
,Year(@DateInProcess) - [YearKey]
&NBSP; ,Cast(年(@DateInProcess)为nVarchar(100)) - [YearName]
)
- 添加一天并重新循环
设置@DateInProcess = DateAdd(d,1,@ DateInProcess)
结束
- 2e)向DimDates添加其他查找值
插入[DWEnrollment]。[dbo]。[DimDates]
([DayKey]
,[USADateName]
  ;,[MonthKey]
,[MonthName ]
,[YearKey]
,[YearName])
选择
[DateKey] = -1
,[DateName] = Cast('未知日'作为nVarchar(50))
,[Month] = -1
,[MonthName] = Cast('未知月'为nVarchar(50))
,[Year] = -1
,[YearName] = Cast('未知年份'为nVarchar(50))
Union
选择
[DateKey] = -2
,[DateName] = Cast('腐败日'为nVarchar(50))
,[Month] = -2
,[MonthName] = Cast('腐败月'为nVarchar(50))
,[Year] = -2
,[YearName] = Cast('腐败年'为nVarchar(50))
Declare @StartDate datetime = '01/01/2000'
Declare @EndDate datetime = '01/01/2010'
-- Use a while loop to add dates to the table
Declare @DateInProcess datetime
Set @DateInProcess = @StartDate
While @DateInProcess <= @EndDate
Begin
-- Add a row into the date dimension table for this date
Insert Into DimDates
Values (
convert(char(8),@DateInProcess,112) -- [DateKey]
, DateName( weekday, @DateInProcess ) + ', ' + Convert(nvarchar(100), @DateInProcess , 112) -- [USADateName]
, Left(Cast(Convert(nvarchar(100), @DateInProcess , 112) as int), 6) -- [MonthKey]
, DateName( MONTH, @DateInProcess ) + ', ' + Cast( Year(@DateInProcess ) as nVarchar(100) ) -- [MonthName]
, Year( @DateInProcess ) -- [YearKey]
, Cast( Year(@DateInProcess ) as nVarchar(100) ) -- [YearName]
)
-- Add a day and loop again
Set @DateInProcess = DateAdd(d, 1, @DateInProcess)
End
-- 2e) Add additional lookup values to DimDates
Insert Into [DWEnrollment].[dbo].[DimDates]
( [DayKey]
, [USADateName]
, [MonthKey]
, [MonthName]
, [YearKey]
, [YearName] )
Select
[DateKey] = -1
, [DateName] = Cast('Unknown Day' as nVarchar(50) )
, [Month] = -1
, [MonthName] = Cast('Unknown Month' as nVarchar(50) )
, [Year] = -1
, [YearName] = Cast('Unknown Year' as nVarchar(50) )
Union
Select
[DateKey] = -2
, [DateName] = Cast('Corrupt Day' as nVarchar(50) )
, [Month] = -2
, [MonthName] = Cast('Corrupt Month' as nVarchar(50) )
, [Year] = -2
, [YearName] = Cast('Corrupt Year' as nVarchar(50) )
推荐答案
如果是这样,您能否显示映射图片以及如何使用这些参数?
If so, could you show the mapping picture and how are you using the parameters?
这篇关于SSiS执行任务加载帮助!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!