CODEWITHSUNDEEP
excelssisetl
Brijesh
Brijesh

Reputation: 13

What is the max Excel File Size(in MBs) which can be imported by SSIS

I want to know the max Excel File size which we can load into db using a Simple ETL SSIS package. If file size depends upon system configs or resources, Then how can we calculate it? In my case I am trying to load an excel file of 500+Mbs. My Package gets hanged even while trying to map columns.

Thanks.

Upvotes: 0

Views: 1003

Answers (1)

Jonathan Porter
Jonathan Porter

Reputation: 1556

The only real limitation 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, and don't forget about all your other applications)

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.

Hope this helps.

Upvotes: 1

Related Questions