DTExec is taking all my memory

I have created a SSIS package that reads 500 text files splits them into 4 raw files then reads them again and writes then to 4 database tables different Tables.

The reason form this is that my raw files have multiple types of records in them and it is only 1 Coolum. I split this out into the different types of records and load whole rows into the database.

ie input 1 txt file

<T6>
1:1000178
3:18148821-00
5:40204043
6:1
17:EX201036259NZ
25:0000304862
</T6>
<T1>
1:18148821-00
</T1>
<T5>
1:1511313
4:18126485-00
8:2006032510230300
17:EX201033399NZ
</T5>
<T6>
1:1511158
3:18084863-00
5:40617044
6:1
17:EX201033969NZ
25:0000302981
</T6>

End up begin rows in the T6 Table

1000178 18148821-00 40204043 1 EX201036259NZ 0000304862

1511158 18084863-00 40617044 1 EX201033969NZ 0000302981

T5 Table gets a new record
1511313 18126485-00 2006032510230300 EX201033399NZ

and T1 Table get a record

18148821-00

Anyway all this works find but I find that the DTExec process work fine until it has used up all the memory in the laptop in general it take 400megs to run this SSIS. I'm wondering am I missing something like don't run in a transaction. I know in the old DTS you could commit on each package and how do I turn all logging off eg what you see in the DOS box (can I do this?) would love some help on this and if anyone want a copy of this ssis package ie your trying to do the same then I'm more than happy to email it.

Check the /Rep option of DTExec to reduce the output.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm

-Jamie

|||

Thanks Jamie,

This has help the logging (if you know can I set this in the enviroment IDE).

Now bigger problems is the memory, at the moment it fails after 700 files due to luck in memory.

Any ideas on how to find where the memory leak is in the SSIS Package?

|||

Just to let other people kno, after some more searching I found that there is a memory leak in the Forloop object, that should be fixed in SP1.

I have download SP1 and tested it and it does appear that there is still a memory leak but only about a 10th of what it was, which now allows me in import 5000 files in one go.

|||

Thanks for the info John. That's an important one to know. I certainly wasn't aware of it.

-Jamie