HELP: How to avoid ridiculous system resource issues when connecting to a huge Excel file

Hi all,

I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.

I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:

Intel Centrino Duo 2.00 GHz

2GB RAM

XP Pro SP2

There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.

Sincerely,

Brian Pulliam

Ok, so it sounds like it wants to read the entire file, a nice feature, and probably unavoidable.

Personally I would be developing and testing against a smaller file that is easier to work with, and easier to manipulate for test cases. Then when the bulk of the dev is ready run a test with the real file.

You may think this is a big issue, and whilst it would be nicer if it behaved better, what if this was a SQL load, woud you expect to dev against a 100GB database? Whilst the metadata would be OK, the simple running of a test would be impractical, so I'd still go with smaller test data during development.

|||

Just one more idea to try...

If you are using "Excel Source" try to use "OLEDB Source" instead and specify Microsoft Jet 4.0 OLE DB Provider.

If you are using Excel 2007, you should use the new ACE provider.

Regards,

Yitzhak

|||

In case if you don't have MS Office 2007 installed on your machine,

here is the link to donwload the connectivity components:

2007 Office System Driver: Data Connectivity Components.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=ja

Regards,

Yitzhak

|||

Thanks much for the reply Darren. I agree that a small subset of data would be great to test with. Actually I would dev against a 100GB database, although I would not pull all the data in my tests.

But your analogy to running a test on a 100GB DB is not very applicable. Firstly, I am not loading an entire DB. My Excel connection is pointing at one worksheet in an Excel 2003 file. And I have not even tried executing this package yet, I am nowhere near that far. Simply renaming the connection to the Excel file takes about 5 minutes before BI Developer Studio is responsive again. Also note that when connecting to a 100GB database, BI does not attempt to load the entire data set in that scenario. Maybe it has something to do with using an Excel file, that SSIS goes all paranoid and wants to look at the whole dang file.

Do you think if I set up a linked server to the Excel file in Management Studio that it might not have this problem? Maybe that would fake it into trusting the data source enough to not to the validation load. I attempted to do this on Friday but did not have much luck. Has anyone done this for an Excel 11.0 workbook?

Thanks again,

Brian