1

I have table with columns Start date and end date and i need to enter values for 2 years.I created a for loop and I am trying to load values for every month.

For Example, My first input start Date is 10/12/2016 end date 11/11/2016. It should be incremented with one month and inserted into table for 2 years.

For Loop Container

InitExpression: @windowStart="10/12/2016"

Evalexpression: @WindowStart<@windowMaxdate

AssignExpression: @windowStart= Dateadd("mm",1,@windowStart)

Execute sql task

SQL command:

Insert into dbo.datetemp 
(WindowStart,WindowEnd ) values (?,?)

My problem is i am not getting values what i am expected and its just returning same windowstart and windowend for every record. And loop is not coming to stop.

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
sai yarlagadda
  • 35
  • 1
  • 8
  • 1
    have you thought of inserting the rows in one ExecuteSQL task instead of a loop? – user1443098 Aug 17 '18 at 18:18
  • i want do this using for loop only i dont want to use direct sql statement – sai yarlagadda Aug 17 '18 at 18:27
  • Post a screenshot of your ForLoop container properties so we can see what you are doing there. – Tab Alleman Aug 17 '18 at 18:36
  • Hi Tab, i just added image , it was in a link. can you see that. – sai yarlagadda Aug 17 '18 at 18:51
  • Just a comment: this is a very convoluted way of achieving this.It would be much simpler to just write a piece of T-SQL and execute it. If you are doing data activities, it's important to avoid loops and procedural code. It is important to understand set based approaches. – Nick.Mc Aug 18 '18 at 01:39

2 Answers2

1

Guys i got the solution.

I have added a expression task inside for loop after execute sql task 

@[User::WindowStart] = (DT_WSTR,24)(DT_DBTIMESTAMP) dateadd("mm",1, 
(@[User::WindowStart]) )

instead adding this expression in Assign expression in for loop editor.

sai yarlagadda
  • 35
  • 1
  • 8
0

You are passing a string value to a @windowStart.

Create a variable @StartDate of type Datetime with the value 2016-12-10. And use the following expression:

InitExpression: @windowStart= @StartDate

Also make sure that @windowStart and @windowMaxdate are of type DateTime

Also check this similar question for more information:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • its the same thing i am getting only one record into table. and the loop is not stoping execution. where execute sql task inserted only one record. – sai yarlagadda Aug 17 '18 at 18:57