ssis error : foreach in foreach loop container

▲
1
▼

Thank you for answering the question.

I check whether the variables contains null, however it's not the cause of null.

Today, I find out why can not work. Because, the variable I set at variable mapping is as same as collection one too. So first loop works but next loop doesn't work.

when I set different variable at variable mapping, it works completely.

I apologize for careless mistake.

I'm using SSIS 2008. I get two kind of data, date(4/1,4/2,4/3) and number(10,20,30) from text file.

I input them at variables as array and object typed.

I tried to loop them by using foreach loop container. I put the array at enumerator from variables. I confirm it loops and show the message 4/1,4/2,4/3 by using msgbox. it success to loop.

I try to loop different variables of number by above same way. it success to show 10,20,30.

howwever, I set the foreach loop container of date in the foreach container of number. the msgbox show 10 4/1, 10 4/2, 10 4/3 at first, but next is empty message, then under error occurred

Error: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80131509 "(null)". This occurs when the ForEach Enumerator cannot enumerate.

more information, when debug, the two foreach loop container is red, but the script task of msgbox in the two containers is green.

I'd like to know why and how to solve the problem.

foreach
ssis
asked on Stack Overflow May 19, 2015 by Matha • edited May 21, 2015 by Matha

2 Answers

▲
1
▼

I had this Problem. I solved it by applying the parameter values to an "Foreach ADO Enumerator" in "Foreach Loop Editor" window.

Foreach Loop Editor

Hope it helps.

answered on Stack Overflow Dec 10, 2019 by SM.Kheyrandish • edited Dec 10, 2019 by CKE
▲
0
▼

I reviewed your updated question and the cause is definitely due to your attempting to enumerate an object variable containing a NULL. As you stated when applying the message box in a script task, one of the values is "empty" and that is when the error takes place and the ForEach loop changes to Red. The ForEach loop changes to Red because it cannot enumerate a NULL.

There are two options for resolving this issue:

  1. At the point where the values are assigned into the object variable (which is an array), apply a condition that checks for null values. If not NULL, add or insert to the object (array) variable.
  2. Apply an expression in the nested ForEach loop that uses the built-in functions to check for a null and change it to something like 0 (zero). You may need to apply the expression on the ForEachEnumerator Property so that the value sent to Variable Mappings is not a NULL. This will require some experimentation.

If you were reading from a database table, this would be easily resolved with a SQL statement applying the ISNULL function to check for a NULL and change it to something else. In your case, you are reading from a text file and need to find a way to programmatically do the same with C# or VB.NET with regard to how an array of values is saved to the object variable.

Hope this helps. Please mark my response as having answered your question if it helped. Thanks.

answered on Stack Overflow May 19, 2015 by user3662215 • edited May 20, 2015 by user3662215

User contributions licensed under CC BY-SA 3.0