For Loop Container SSIS AssignExpression

Question

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.

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.McDermaid, Aug 18 '18 at 01:39

score 1 · Answer 1 · answered Aug 17 '18 at 19:39

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.

score 0 · Answer 2 · answered Aug 17 '18 at 18:49

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:

How do I loop through date values stored as numbers within For Loop container?

answered Aug 17 '18 at 18:49

Hadi

34,224
11
61
121

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

For Loop Container SSIS AssignExpression

2 Answers2