February 20, 2009 at 9:37 pm
I have a road block that I need to find a work around for. I work on a system where the Databases reside on a soley database server (SQL 2005).
I need to run a stored procedure on a nightly basis and export the results into a template excel file and put out on shares. Sounds simple right? Here's the problem. In order to do this through SSIS, I need to install Microsoft.Office.Interop.Excel on the server so I can reference the assembly in the SSIS package. Unfortunately, this is a huge company and trying to get this installed on the database server is not going to be allowed. So, does anyone have any idea on how to do this either w/o SSIS or w/SSIS working around this problem. Or, is this really a problem?
Thanks!
February 21, 2009 at 12:19 am
Why not export to a flat file (http://msdn.microsoft.com/en-us/library/ms141668(SQL.90).aspx) as delimited or fixed width? Those files would then be readable by Excel without any issue.
[edit]posted the sql 2008 link accidently, corrected with the sql 2005 flat file reference page[/edit]
February 21, 2009 at 7:57 am
That's an idea...and might work...
The excel file is formated with headers, font, column headers are rotated in some cases. Now, the template just sits there already looking the way it should. I'm not an excel wiz so, is excel capable enough to ust import a text file into the already formated template? I'm assuming it is. Then is there a way to progmatically have it do that when a new file is put out in the directory of the of the template?
February 23, 2009 at 4:45 am
Can you make the Excel template use a second worksheet as it's data source? If you have a heavily formatted table in your workbook, use another worksheet with an unformatted table as the source for your formatted table's data.
Then, you can simply export to the second worksheet using SSIS and an excel file destination (or the jet driver).
February 24, 2009 at 7:14 am
Do you have a Reporting Services instance somewhere? That might be the easiest solution - a scheduled report.
Importing data could be done too - an Auto_Open macro comes to mind.
And an Excel workbook could also be coded to execute the sp and return the data directly.
Some of the solution also might depend on if you (or the user) expects to keep historical copies.
Greg E
February 25, 2009 at 3:58 pm
Assuming you can setup a ODBC connection on the box with the Excel Template, let the stored procedure write the report data to a temp table. In Excel at 'Data/Import External Data/New database query' connect directly to the temp table. A macro can update the spreadsheets when it gets opened.
February 26, 2009 at 8:20 am
We were originally using Reporting Services but the export process from there wasn't going to produce the spreadsheet the way the customer wanted it. So, we decided to go straight from SSIS to excel.
February 26, 2009 at 8:23 am
Thanks. We're actually trying to avoide updating the spreadsheet upon opening. The goal is to just have the finished spreadsheets sitting out on a share.
We decided to write a C# app that connects to the database and grabs the recordset. Then writes it out to a blank spreadsheet. The app then will open up the template and the code inside the template will grab the data, populate a new spreadsheet and format it appropriately, save the file and put it out on the appropriate share. It may be more work than necessary but it seems to work.
Thanks everyone!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply