dts that dumps the result of a view into an excel file
-
hi,
i need to send a report to a client in an excel file. i can't use crystal report because i need a formatted excel file (with page headers, footers, bold characters and stuff).
i thought of creating the report in DTS. i added a connection to sql server where the source will come from. i added an excel connection that points to the excel file that has all the formatting stuffs on it. i also added a send email task.
i run it once and it went fine. the next time i tried, i am getting the following error:
External table is not in the expected format.
When I tried to open the excel file, true enough, I am getting an error. But I can still open it. What I can't do is use it in my DTS. So I copied the original file that I got and run my DTS again and everything was fine. Its when I attempted to run it again that I'm getting the same error.
Any ideas?
ann
-
You can have basic formatting in your Excel file by setting up the formatting in a blank workbook and copying it each time you run the DTS package.
For more formatting, you'd have to use something like Reporting Services as DTS is not a reporting tool.
--------------------
Colt 45 - the original point and click interface
-
You could create a formatted spreadsheet which gets its data from a link to the spreadsheet output by your DTS package. Every time you open your formatted sheet, refresh the link. Then copy the file, remove the link and send it off.
--
Scott -
What I have done in the past, is to implement a SP I found off the internet.
Its called sp_Write2Excel. If you google it, you should find it.
In short, it executes the sp_addlinkedserver command, linking to an Excel spreadsheet. Then after it is linked to the spreadsheet, there is a commnd that will insert data accordingly using the following syntax:
Insert Into <Linked Server Name>...<Worksheet Name> <Column List> <SQL Query>
Just be sure that when you are done, you execute:
sp_dropserver <Linked Server Name>
I have an example if you need more info. There are some tricky details pertaining to the blank Excel file you are inserting into, and arranging the column names. If you find the SP i referred to above, it explains everything. Feel free to contact me directly, and I can email the files to you.
deanc24 at hotmail dot com
Good Luck!
-
Even better, have Excel get the data.
If you didn't install MS Query with Excel, then install it.
Then, do Data->Get External Data. You can get the data from an ODBC data source.
Save the workbook as a template.
Then, to get the data next time, create a new workbook based off of that template, click on the red "!" button to refresh the data, save the workbook with a real file name.
(or automate the above from VBScript).
Most MS things that export to Excel seem to do a rather crappy job with it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply