Hello everyone I created package based on this instructions (Loading the Output of a Package into Another Program http://msdn.microsoft.com/en-us/library/ms135917.aspx) and it works well.I was able to retrieve data from datareader using DtsCommand and DtsConnection. But when I put the TaskFlow inside Foreach Loop container I get error "The specified data flow component was not found in the package". It seems to me that foreach loop container is scoping my datareaderdest. And I do not know what is namespace of this scope. Does anyone know how to name datareader destination for DtsCommand.CommandText which is inside Foreach loop container?

I do not understand how can you consume the data while the package is looping? This whole thing seems not designed as I expect it to. What goal are you trying to achieve with your package?Arthur My Blog

Arthur, thanks for your reply Well, I am going to retrive data from several db sources and save it to the datareader for consuming in code. does it make sense so far? EDIT: I dont think I am doing something "outstanding". I feel I am on the right track. I have the same logic like this one http://msdn.microsoft.com/en-us/library/ms135917.aspx but for several db. To be short 'I need to retrieve data from many db sources'

Understand what you are coming from, You have so many datasources you try to use the ForEach loop to set the con string dynamically? If so try setting Delay Validation to false.Arthur My Blog

Arthur You are correct. Foreach loop is iterating over connection strings and binding to connnection manager via expression. (ssis expression is the realy powerfull stuff, btw) 1. DelayValidation is allready set to False. 2. And I double check the name of the DataReaderDestination, no typos, no errors. I am discouraged. Any ideas?

1. Where is the code consuming the data reader destination located? Is it inside Script Task, which executes inside the same For Each Loop? 2. Do you have constraint between the Data Flow Task where the data reader resides and the script task where you consume the result? 3. When you say it doesn't work, do you get specific error and what is the error?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

CozyRoc thanks for your reply 1. No, the C# code is outside of the package. this is the external consumer (see code block) no Script Task at all. 2. Not required 3. Yes, I do, as I mention the exception is "The specified data flow component was not found in the package". ps. I used CommandBehavior.Default as well as others values and I allways get the same result, so CommandBehavior.CloseConnection is not a cause of the problem! so again. 1. I have package without foreach loop . And it works fine in BI Studio as well as C# code. 2. I have package with foreach loop (very close to #1). And it works only in BI Studio. I couldnt invoke DataReaderDest from the C# code. private static WkzReportDataSet.WkzDataRecordDataTable GetDataFromSapSource(string package, string startDate, string endDate) { var result = new WkzReportDataSet.WkzDataRecordDataTable(); using (var connection = new DtsConnection { ConnectionString = package }) using (var command = new DtsCommand(connection) { CommandText = "DataReaderDest", CommandTimeout = 10000 }) { connection.Open(); command.Parameters.Add(new DtsDataParameter("User::StartDate", startDate)); command.Parameters.Add(new DtsDataParameter("User::EndDate", endDate)); using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { result.AddWkzDataRecordRow( Convert.ToInt64(reader["LIEF_NO"]), Convert.ToString(reader["SAP_KEY"]), Convert.ToString(reader["DESCR"]), reader.IsDBNull(reader.GetOrdinal("WARENEINGANG")) ? new DateTime?() : Convert.ToDateTime(reader["WARENEINGANG"]), Convert.ToString(reader["DESCRIPTION"]), Convert.ToInt64(reader["VEREINB_NO"]), Convert.ToDateTime(reader["GUELTIG_VON"]), Convert.ToDateTime(reader["GUELTIG_BIS"]), Convert.ToDecimal(reader["BETRAG"]), Convert.ToDecimal(reader["ACCRUALS"]), Convert.ToDateTime(reader["VEREINBART_AM"]), Convert.ToDateTime(reader["ERSTELLT_AM"]), Convert.ToString(reader["ACC_DESCRIPTION"])); } } } return result; } EDIT: I know there are many others workarounds for this task, but I would like to consume ssis package without needs of "manual" loops. this one I am using in my current production code. Parallel.ForEach(Context.Connections, ()=> new DataTable(), (connection, loop, subResult) => { var sap = string.Format(@"/CONNECTION ""Sap Connection Manager"";""\""{0}\""""", connection); var status = string.Format(@"/CONNECTION ""Status Connection Manager"";""\""{0}\""""", Context.DtsWkzStatusConnection); var packageDtsx = string.Format(@"/SQL ""\WkzData"" {0} {1} {2}", Context.DtsPackageServer, sap, status); try { subResult = GetDataFromSapSource(packageDtsx, request.PeriodFrom.ToString("yyyy-MM-dd"), request.PeriodTo.ToString("yyyy-MM-dd")); } catch (Exception ex) { errors.Add(string.Format("Error in '{0}'. {1}", connection.Name, ex.Message)); } return subResult; }, sub => { lock(locker) { table.Merge(sub); } } );

I don't think you will be able to implement what you are trying to do. Open Microsoft.SqlServer.Dts.DtsClient assembly in Reflector and go to the following method: DtsCommand.FindComponent. Do you see how it checks for Sequence class inside the loop? The For Each Loop container is implemented in C++ and therefore it cannot be casted to Sequence class. The method had to check for IDTSSequence interface instead.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

I don't think you will be able to implement what you are trying to do. Open Microsoft.SqlServer.Dts.DtsClient assembly in Reflector and go to the following method: DtsCommand.FindComponent. Do you see how it checks for Sequence class inside the loop? The For Each Loop container is implemented in C++ and therefore it cannot be casted to Sequence class. The method had to check for IDTSSequence interface instead. SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ Thanks, I believe you're right. - Sergey