Foreach loop runs out of memory

Hi

I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.

[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

Thanks for the reply, the 5000 row record set has two fields one integer the other nchar(5). The second recordset gets re-used in the loop so this will only have 10 rows max at a time and has 4 fields. Unfortunately I cannot change the Loop to SQL, I do not have any knowledge of set based operations, is this similar to a loop

Regards

ADG

Thanks for the reply.

Inside my Foreach loop container I have an Execute SQL task which uses the two variables from the Recordset being scrolled through, and populates my second recordset. Do I need to explicitly close the recordset inside the loop If so how should the recordset be closed, is this a Script task

Many thanks,

I will change the script component and give it a try. I am new to SQL Server / SSIS so it looks like II have made another beginners error.

I have tried to close the recordset but the close method does not appear to be valid in the main code,( I tried Me.Variables.MyRecordSet2.close). Do I need to override one of the Class methods The code I used is modified from one of Jamie Thomsons blogs and is as below:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

'

Dim olead As New Data.OleDb.OleDbDataAdapter 'Define an ADO.Net data adapter

Dim dt As New Data.DataTable 'Define an ADO.Net DataTable

Dim row As System.Data.DataRow 'Define an ADO.Net DataRow

Dim lastSI, SI As Int32

Dim strU(50) As String

Dim strU2(50) As String

Dim dblFactor(50) As Double

Dim x, y, j, k As Int32

Dim isFirst As Boolean

Dim strLComb, strComb As String

olead.Fill(dt, Me.Variables.MyRecordSet2) 'Populate our DataTable from the adapter

lastSI = 0

strLComb = " "

SI = 0

x = 0

isFirst = True

For Each row In dt.Rows 'Iterate over the rows in the table

strComb = row("SI").ToString & row("U").ToString

If strComb <> strLComb Then

If isFirst Then

isFirst = False

Else

For y = 1 To (x - 1)

For k = 2 To x

With OutputBuffer

.AddRow()

.Calculated = True

.Factor = dblFactor(k) / dblFactor(y)

.SI = SI

.U = strU2(y)

.U2 = strU2(k)

End With

Next

Next

End If

SI = CType(row("SI"), Integer)

strLComb = row("SI").ToString & row("U").ToString

x = 1

strU(x) = row("U").ToString

strU2(x) = row("U2").ToString

dblFactor(x) = CType(row("Factor"), Double)

Else

x = x + 1

strU(x) = row("U").ToString

strU2(x) = row("U2").ToString

dblFactor(x) = CType(row("Factor"), Double)

End If

Next

For y = 1 To (x - 1)

For k = 2 To x

With OutputBuffer

.AddRow()

.Calculated = True

.Factor = dblFactor(k) / dblFactor(y)

.SI = SI

.U = strU2(y)

.U2 = strU2(k)

End With

Next

Next

dt.Dispose()

End Sub

End Class

Thanks again,

Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output

ADG wrote:

Thanks again, Can you point me in the right direction with the custom transformation. I have created the OLE DB which will return between 2 and 10 rows, I need to store this in an array to process. I assume that each row will pass into the script via Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) . How do I catch all the rows then perform the output

Take a look at this topic in books online: "Creating an Asynchronous Transformation with the Script Component"

Since your are not simply passing each row through, you'll need to use an asynchronous transform.

Many thanks for the post. I have incorporated some of the above into my final package

However, I have gone back to using a for each loop to generate a OLE DB source, which feeds an asynchronous script transformation . The above works but does not create all the records that I needed. The process of adding rows to the table in turn creates more possible row combinations. My memory issue was eventually resolved by installing SP2. My package now works, but is slow. Below is an exapmle of my data problem ( data for two SKU's as in the original source, and as in my final table )

Becomes

The project takes about 30 minutes to run with 21,500 input rows and has 135,900 final rows. I guess if this process could be done with SQL it would be far more efficient, but I cannot see how this can be done when each row added can gerate more possible combinations. If anyone out there can see away of performing the above transformation with pure SQL I would be grateful for your ideas.