June 23, 2008 at 10:43 am
Hi there,
There is a little something I have noticed a while ago and that I haven't figured out how to avoid... When creating and populating an excel file in SSIS the file being recorded is always roughly twice the size of an equivalent file if created & saved from Excel.
So each time my package runs I manually open the output file and click save (without changing anything else...). Doing this manual save reduces by half the file size...
Do I need to update my JET driver or something?
You should be able to reproduce this easily.
June 23, 2008 at 11:03 am
Try saving manually with some of the compatibility options. I think the Jet driver saves in a compatibility mode and it may be different than your default save options.
June 23, 2008 at 11:14 am
This happens if you reuse the same sheets/file over and over again. I'm not sure the exact reason why, but I know each Excel cell that has been modified (i.e. had data entered into it) takes up addition space - regardless of if there is currently data in there now. What probably happened is that you had a large data set, say 50000 rows, but you average around 10,000 row. Excel still saves it like there is 50000 rows of data.
That's a rough explaination, but I know if you delete all empty rows (not with the delete key, but actually select the cells/rows, right click and select delete), the problem goes away (temporarily).
June 23, 2008 at 11:59 am
about deleting empty empty rows/cells -> I don't even have to delete anything, i just open the file and re-save it from Excel, automatically the file size goes down.
compatibility options -> I will look into this and post later if I find a solution
June 23, 2008 at 12:01 pm
mmm... How to change the compatibility options that JET uses?
June 23, 2008 at 12:10 pm
I don't think you can, but you may want to look into the available options on the connection string. Create a .udl file, open it, and look at the connection string options for the Jet/Excel connection.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply