June 24, 2005 at 7:34 am
Hello. I have a problem with file size when creating Excel files out of DTS. One in particular which creates a file with the results of a simple select query which outputs several thousand rows is huge. When the file is created the file size is 7MB but if I open the file manually and simply save it the size changes to 3.6MB. This file is meant to be automatically emailed and obviously 7MB chews up much more bandwidth than 3.6MB.
I have tried outputting to Excel 5.0 format instead of Excel 97-2000 format but it makes no difference. (I have not tried any lower Excel versions.)
In the DTS package the steps are 1. Drop table, 2. Create Table, 3. Transformation Step, 4. Email attachment
( I do the drop table instead of totally re-creating the file in order to retain some formatting in the header rows)
June 27, 2005 at 8:00 am
This was removed by the editor as SPAM
June 27, 2005 at 9:19 am
I reckon that the original file you created was longer than the file you have now and excel is keeping the empty rows at the bottom and columns at the side. Hence, your file is too big.
Open the spreadsheet and Ctrl goto end to see where it thinks the end of the file is.
June 27, 2005 at 9:45 am
Thanks for your reply. That doesn't seem to be it. Ctrl - END took me to the same cell before and after resaving yet the file size decreased dramatically after the save.
June 27, 2005 at 10:16 am
Have you tried adding another step in activex code that just opens and saves the spreadsheet again at the end and see if that makes any difference. Try using the filesystemobject
I've never seen the behaviour you have mentioned before. It must be padding out the spreadsheet somewhere?????
Code in case you haven't got it:
'----------------------------------------------------------------------
Option Explicit
Function Main()
Dim Excel_Application
Dim Excel_WorkBook
' Create and set up the Excel File to Import
Set Excel_Application = CreateObject("Excel.Application")
' Open Excel Workbook
Set Excel_WorkBook = Excel_Application.Workbooks.Open("FullFileName")
Excel_WorkBook.Save
' Destroy Excel Objects
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing
Main = DTSTaskExecResult_Success
End Function
'----------------------------------------------------------------------
June 28, 2005 at 7:43 am
I'll try adding the activex script. I sure would like to know what is causing the problem though.
Thanks again for your help.
June 28, 2005 at 8:19 am
Let us know if you do find out. Just a thought, but perhaps the columns in the db are char and it's padding out the output from sql server but excel knows when opened manually and truncates it??
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply