I'm Using Execute Sql Task To Update Time Dimension I'm Using This Code
declare @i int=isnull((select max(id) from DIM_DATE)+1,1)
declare @Date Date=isnull((select [date] from dim_date where ID=@i-1),(SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) , 0)))
declare @Count int=((isnull((select max(id) from DIM_DATE)+1,0)) )+(select day(eomonth((select DATEADD(day,1,@Date)))))
if((select count(*) from DIM_DATE)=0)
begin
insert into DIM_DATE values( @i,@Date,(select year(@Date)) ,(select datepart(Quarter,@Date)) ,(select Month(@Date)),(select format( @Date,'MMMM')),(select day(@Date)),(select datepart(weekday,@Date)),(SELECT DATENAME(dw,@Date)),
(select year(@Date)),(select datepart(Quarter,(@Date))))
End
while(@i<@Count)
begin
insert into DIM_DATE values( @i ,(select dateadd(day,1,@Date)),(select year((select DATEADD(day,1,@Date))))
,(select datepart(Quarter,(select DATEADD(day,1,@Date)))),
(select Month(DATEADD(day,1,@Date))) ,(select format(dateadd(day,1, @Date),'MMMM')),
(select day(DATEADD(day,1,@Date))) ,(select datepart(weekday,@Date)) ,(SELECT DATENAME(dw,@Date)) ,
(select year((select DATEADD(day,1,@Date)))),(select datepart(Quarter,(select DATEADD(day,1,@Date)))))
set @Date=(select dateadd(day,1,@Date))
set @i+=1
End
When I try it on sql server It's Work But in SSIS It Inserted Only First Row.
This is a screenshot of the package control flow: