thames


I'm having a problem where I'm using a Execute SQL Task to retrieve a dataset and storing that in an object variable. Then on success of that execute sql task I use a foreach loop task to go through the dataset and do 2 tasks inside the foreach loop. When I execute this package I have ~12 records in the dataset however when I get to the foreach loop in the 2nd iteration it keeps repeating it. It acts like it is stuck on the second record (tuple) and never goes on. I'm using an ForEach ADO Enumerator in the foreach. I've even set a breakpoint on each iteration and no task fails in side the foreach loop. I'm completely perplexed why it will iterate to the 2nd record but get stuck there in an endless loop. I've tried this on 2 different computers (with different data values) and the same thing happens. Does anyone have any suggestions


Re: Foreach endless loop

thames


Ok something wierd is going on. When I disable the 2 tasks that are inside the ForEach loop it iterates over all obejects in the ADO dataset. However when they are enabled it gives me the endless loop on the 2nd through the loop.




Re: Foreach endless loop

Phil Brammer

Are you sure it's looping and not just stuck on something






Re: Foreach endless loop

thames

I'm not sure what it would be stuck on since I'm breaking on each iteration and everything is completing successfully. My files are being built fine so not sure why it gets stuck on the 2nd iteration. I'm not forceing an execution result.



Re: Foreach endless loop

Phil Brammer

thames wrote:
I'm not sure what it would be stuck on since I'm breaking on each iteration and everything is completing successfully. My files are being built fine so not sure why it gets stuck on the 2nd iteration. I'm not forceing an execution result.


I would agree. I just want to see if you're sure that using the second value from the ADO recordset, that the foreach loop is continuing on and on and on...





Re: Foreach endless loop

Ravi G

I've seen the exact same behariour. It would keep looping through the 2nd task exactly like thames mentioned.

I was trying to load a bunch of flat files in a foreach loop and the list was in an ADO record set. It kept loading the second file over and over again and never exited the loop.

I thought I was doing something wrong and went with another approach. I used a multi-flat file connetion instead.

Sorry, I don't have a solution but thought I'd support your cause. ;-)





Re: Foreach endless loop

thames

How would i tell if something is happening, like getting stuck I noticed that in the Watch for my variable that stores the current ADO record value turns red on the second iteration but there after turns back to black. Does this mean anything



Re: Foreach endless loop

thames

Thanks Ravi for the support!

Any others Any hints/suggestions I just can't figure out why when I disable all tasks inside the foreach it iterates through all but then when they are enabled it creates an endless loop on the second iteration. Does the color of the value of the variable as mentioned in my 4th post have anything to do with it





Re: Foreach endless loop

thames

The other wierd thing is that I've done this in a case study in one of the SSIS books and it worked fine. I also implemented another foreach over a dataset in another package and it iterates over all of the items just fine. The only difference is the kind of tasks inside the foreach. The one that is getting stuck in an endless loop is using 2 Execute SQL Tasks inside the foreach loop. Was this the same for you Ravi



Re: Foreach endless loop

Phil Brammer

You aren't accidentally touching the same ADO recordset inside each of the foreach loop's Execute SQL tasks, are you





Re: Foreach endless loop

thames

An example of what I'm doing is:

I'm just taking the dataset of table names retrieved from a Execute SQL Task and enumerating over the each table name retrieved. I then create an expression that generates a sql statement that insertes the enumerated table name variable value into another variable that I Execute as a SQL Task inside the ForEach loop.

  1. Execute SQL Task: Get Table Names and put into "Tables" Object Variable
  2. ForEach [record]:
    1. ADO Record put first column value "TableName" into a variable
    2. Generate Expression Variable called "SQLCMD_1" that inserts the "TableName" into a SQL statement. i.e. something like "select * from " + @[User::TableName]
    3. Generate Expression Variable called "SQLCMD_2" that inserts the "TableName" into a SQL statement. i.e. something like "select * from " + @[User::TableName]
    4. Execute SQL Task from "SQLCMD_1" variable. If successful go to 2nd SQL Task, if failure then fail parent.
    5. Execute SQL Task from "SQLCMD_2" variable. If successful start next iteration (implied since this is the last task in the foreach loop), if failure then fail parent.
    6. Loop till end of "Tables" dataset
  3. Perform some more operations. However because of the endless loop these never get executed unless the Execute SQL Task inside the ForEach Loop are disabled.




Re: Foreach endless loop

thames

Well came back to work this morning and it still does the same endless loop. I noticed that my expression variable for SQLCMD_2 was referencing another variable in the form of @[TableName] instead of @[User::TableName]. I changed that but still loops endlessly on the 2nd value in the dataset...



Re: Foreach endless loop

thames

I've switched between OLEDB and ADO connections in Execute SQL Task that gets the table names and still no luck. If it wasn't such a pain to recreate all the variable I would create a new package. Is there an easy way to copy variables



Re: Foreach endless loop

Ravi G

If you create a copy of your package, all your variables will go with it too.



Re: Foreach endless loop

thames

Yeah I thought of that, but wanted to have a fresh start incase something wasn't being parsed right. I would still like to know what would cause the ForEach loop to get stuck in an endless loop on the second record. It would be nice to have Jamie Thompson or someone from SQLIS.com or Microsoft SSIS to post an answer. I have a feeling I'll probably run into this again since the same package gets stuck on 2 different servers and my dev machine. However doesn't get stuck when all tasks in the ForEach loop are disabled.