0

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:

enter image description here

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
Ammar Kamil
  • 1
  • 3
  • It's Also Work When I Click On Execute Task (In Right Click) But The Problem Is When I Run All The Package – Ammar Kamil Oct 14 '19 at 10:14
  • This may not fix issue, but that is a lot of code for execute SQL, I would suggest for managing code to put it in Stored Procedure and just call that. Easier to manage, make updates to (you wont have to edit package every time you need to change it) and you can call this from other places if needed as well. Plus an issue with running from SSIS should not be there if your just calling a SP – Brad Oct 14 '19 at 15:15
  • I try it And It's Not Working to – Ammar Kamil Oct 14 '19 at 20:24
  • can you show me how you designed the package? (may be screen shot of it) – umair Oct 15 '19 at 02:33
  • I Do in The Answer Section – Ammar Kamil Oct 15 '19 at 05:45
  • What does `Delete old dimension` do? Are any of these other scripts modifying data in the date dimension? – Mark Wojciechowicz Oct 15 '19 at 18:27
  • The Reason of putting delete old dimensions is if one of the branch are modify products or customers or users i should search compare what are changing and You now that's taking along time so i delete old dimensions and reset the identity and other scripts are not modify the date – Ammar Kamil Oct 18 '19 at 09:01

0 Answers0