Hello, I wonder if someone can put some light on an issue I encountered in a package. I need to run two foreach-containers (using the ADO.NET iterator over two package variables of type object filled before) in a package. The item iterated is one "easy tiny" or perhaps "easy small" dataflow, nothing fancy. Reading from a couple of tables, union, aggregate, store. If I chain the foreach containers sequentially, meaning the second foreach-component starts after the first signals success everything is just fine. The two containers could run parallel, there are enough resources available. If I do remove the success-constraint so that both loops run parallel the package crashes out quite hard: No post-executes events for containers or dataflows, no PackageEnd event for for packages (there are two packages inside the other as sub package) logged. The only thing I see is that if executed inside Visual Studio is "Error Code: unknown Error" and "Return Code: -one billion something" (exact error number can be reproduced if needed) I made sure that the foreach loops are using different variables for the actual value currently used and the collection to run over, so this should not cause the problem. The two dataflows that execute are targetting the same source and destination databases, but there should be enough resources to support that. No deadlocks or timeouts observed on the database side. Anybody have any idea? Should foreach ADO-iterations be able to run parallel? Thanks! PS: System running is Windows 2003 SP2, 2x Quad Core CPUs, 20 GB Ram. SQL Server is 2005 SP3 with Hotfix 7 or 8. Visual Studio BIDS is 2005 SP1 and Hotfixes.

Is there a table lock on the OLE DB destination?My Blog "Karl Beran's BI Mumble"

Does Source or destination table same? or did you used source of one data flow as a destination in another or reverse?http://www.rad.pasfu.com

It is not an issue with the foreach loop but with the source and destinations used in ur data flow tasks. Tweet me..

Thanks for all answers and suggestions so far, I'll try to add some info to each: > Is there a table lock on the OLE DB destination? Yes, both data flows use table locks, but they are writing to different tables (in the same database though, using the same connection manager) > Does Source or destination table same? or did you used source of one data flow as a destination in another or reverse? Each data flow is independent of the other, they do not use results produced by the other. As said, the destination tables are different, the source are 7 stored procs for each flow (14 in total) which all - in one way or the other - read from one single huge (500 million - 1.5 billion rows, 250 GB) table. > It is not an issue with the foreach loop but with the source and destinations used in ur data flow tasks. That's easy to say... I checked and double checked that all sources and destinations are wired up fine and do not conflict. Each data flow works for himself alone, and they work if they run in sequence one after the other. There are no transaction involved - all containers have the default setting of supported, and nothing previous in the packet does start a transaction, so there should be no issue there. If it WAS a problem with the source or destination I would expect a graceful exit. This does not happen. The package crashes hard: - In Visual Studio it just freezes, some boxes green, some yellow (no reds!) and the bottom line reads: Package execution finished. No error, info nothing given. Work is not completed of course. - The dump to a text file looks like this: there is the start of the outer package and the start of the inner one. No additional lines, no package ended, no error, info, warning, work not completed. #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message PackageStart,TEST_DW,NT-AUTORITÄT\SYSTEM,FuelleDataMartFakten,{A4D51535-5F9D-446F-9DC5-5991FE013B66},{BCD94758-3A2B-4FA0-9A03-99A789A66491},9.11.2010 8:57:54,9.11.2010 8:57:54,0,0x,Beginn der Paketausführung. PackageStart,TEST_DW,NT-AUTORITÄT\SYSTEM,FuelleDataMartFaktenStufe2,{14E815BC-AE45-49E2-A143-FECA2FB7A435},{F99EF0D7-049E-4EB4-A805-44F26815CC41},9.11.2010 8:57:56,9.11.2010 8:57:56,0,0x,Beginn der Paketausführung. - Finally, if executed through SQL Server Agent Job, the job gives back SOME information: Ausgeführt als Benutzer: ''TESTDW02\SYSTEM''. Microsoft (R) SQL Server-Paketausführungsprogramm Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. Alle Rechte vorbehalten. Gestartet: 8:57:22. Der Rückgabewert war unbekannt. Der Prozessexitcode war -1073741819. Fehler bei Schritt. Sorry that the error messages are German language. I would translate them to you - but there is nothing to translate. All it says is Returncode unknown, process exit code -1073741819. Error on step. This does not look like a controlled exit which I would expect if it is just a problem with sources or destinations. It might be, but what could it be? I tried Google for this number, but strange enough nothing came up. I did some more test just this morning and removed the destination: table bulk load option and replaced it with destination: table or view (so one insert statement after the other): No change, same behaviour and crash-exit. Quite sure it is NOT the destinations. Thanks! Ralf

You should turn on package logging, and make it fairly verbose. That will give you more information to work from... AFAIK, the only problem with ADO.Net variables being used in a Foreach Loop is that you can't use the same memory construct to run both loops. It sounds like you've accounted for that - but I just want to double-check that this isn't it either. Make sure that you generate the contents for those recordsets completely independently! Can you make a test run, and use a hardcoded list of values instead of the recordset? Just to see if it's the enumerator/loop causing the problem, or something else... Talk to me now on