Loading 1000s of small csv files with SSIS crashes BIDS

Question

Loading 1000s of small csv files with SSIS crashes BIDS

Tempest

SSC Veteran

Points: 241
More actions
February 6, 2012 at 2:16 pm

#252490

Hi I have a curious problem. I am trying to load many 1000s of small csv files into a table with an SSIS pkg. My process works just fine but after processing around 4000 files the process crashes Visual studio BIDS.
The error in my windows event viewer gives an out of memory error.
Any one got any ideas how I can prevent the out of memorry error / flush it so it can loop through all the files without crashing.
The files are only 1-5kb in size but I have 100s of 1000s to load.
Any ideas anyone?..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

How do you load those files? In parallel or one after another?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Also, check this KB article:

The SSIS 2008 runtime process crashes when you run the SSIS 2008 package under a low-memory condition

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Tempest SSC Veteran Points: 241 More actions · Answer 3

hi i have a loop running set to attach each file to a dataflow task and load it it runs really fast but after 4000 files or so it crashes BIDS .

Tempest SSC Veteran Points: 241 More actions · Answer 4

I read the articel many thanks for that, but it doesnt seem to apply to my situation. I am running the pkg from within Visual Studio on my local PC which is running XP. it loops each file assigns the name to a variable attaches the file to a file connection and runs the file into the database. i thought about usnt the multiflatfile connection but on 100,000+ files???

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

I've got the link from this thread:

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/8426a046-ff5c-4d3a-84c0-045a3c1d4138

It appears that someone is having the same issue.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HowardW One Orange Chip Points: 29109 More actions · Answer 6

Why do you need to run the whole thing in BIDS? After you've debugged it with a smaller number of files, couldn't you just execute the package outside of the debug environment? E.g. run it from the execute package utility, schedule it in SQL Agent or use "Start without Debug" in BIDS (CTRL+F5)

The debug environment isn't really designed for huge, long running operations.

Tempest SSC Veteran Points: 241 More actions · Answer 7

HawardW you the man.. simples when you think about it...

one gets used to using bids as its fine for 99% of tasks one wants to do.. happily loading files now.

thanks