Flat File size limit?

Question

Post reply

Flat File size limit?

kvn-427408

Old Hand

Points: 356
More actions
April 30, 2007 at 10:54 am

#173880

I'm new to SSIS and I've been loading data from multiple flat file sources successfully. File sizes ranging anywhere up to about 5GB. But I've hit a snag trying to load a 23GB flat file with 169 fields. When I try running the package, the package execution process abnormally completes only loading a portion of the records. I also noticed it's loading data very very slowly when it is running. I was just wondering if there is a file size limit that SSIS has when reading in flat files. Will this file need to be broken into pieces?

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

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

brodak SSC Enthusiast Points: 165 More actions · Answer 1

brodak

SSC Enthusiast

Points: 165

September 13, 2011 at 12:27 pm

#1380877

did you get an answer for this?

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

brodak (9/13/2011)
did you get an answer for this?

The only real limition is the size of the machine's memory (RAM) where the package is running on, as SSIS loads the data into memory.

Thus, if you only have 2GB of RAM, I wouldn't try to load files bigger than 1 GB. (you must have RAM left for SQL Server to operate, not forget all other applications)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Also remember if you're not pipelining your data flows properly, and you have blocking parts like Aggregate or SQL Command objects, then you are going to be loading way more into memory than you should be.

The file size is not as important if you have no blocking parts. SSIS won't load the entire object into memory, and you can specify how much it uses. But if there are blocking parts, then it will need the entire object in memory.

Note that another big memory hog could be Lookup tasks with Full Caching - these can take large amounts of memory up if you are loading big tables.

brodak SSC Enthusiast Points: 165 More actions · Answer 4

Thanks everyone. After all that, the biz decided to 'forget about that 27 GB flat file'. It went from high priority, to never mind! Haha!

We did learn a lot in the process, and this will help in the redesign of the package that is already in the works.

I'm now curious what is an average Memory capacity on SQL servers?

Also, what is the advantages of a bulk insert task? It isn't used here much, if at all.

We process lots of larger and larger files as our fortune 20 company gets bigger. Anticipating needing this knowledge soon...

kramaswamy SSCoach Points: 18181 More actions · Answer 5

There is no "average" memory capacity on SQL Servers 😛

I've worked with some that have as little as 2GB of memory, and some that have as much as 16GB. I've also seen many that have far more than 16GB.

Bulk Insert is great for ... well, doing exactly what it says. It commits data to a table *far* faster than a standard INSERT INTO does. It's best used when you're just dealing with dumping data into a table, and not requiring any processing beforehand.

SSIS uses bulk insert unless you specifically tell it not to, because it pipelines all the data in and does all the processing before the bulk insert phase.

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

Koen Verbeeck (9/14/2011)
brodak (9/13/2011)
did you get an answer for this?
The only real limition is the size of the machine's memory (RAM) where the package is running on, as SSIS loads the data into memory.
Thus, if you only have 2GB of RAM, I wouldn't try to load files bigger than 1 GB. (you must have RAM left for SQL Server to operate, not forget all other applications)

Hey Koen,

This is incorrect. The SSIS is using streaming approach (processing blocks of data) and can process unlimited amounts of data, if the SSIS package is designed properly.

So the correct answer is you can process unlimited amounts of flat file data. However you have to be careful what kind of partially blocking asynchronous transformations you use in your data flow.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

CozyRoc (9/14/2011)
Koen Verbeeck (9/14/2011)
brodak (9/13/2011)
did you get an answer for this?
The only real limition is the size of the machine's memory (RAM) where the package is running on, as SSIS loads the data into memory.
Thus, if you only have 2GB of RAM, I wouldn't try to load files bigger than 1 GB. (you must have RAM left for SQL Server to operate, not forget all other applications)
Hey Koen,
This is incorrect. The SSIS is using streaming approach (processing blocks of data) and can process unlimited amounts of data, if the SSIS package is designed properly.
So the correct answer is you can process unlimited amounts of flat file data. However you have to be careful what kind of partially blocking asynchronous transformations you use in your data flow.

That is one big IF 😀

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