0

I frequent this site a lot but have never posted but here goes! I am fairly new only about a month in on the job but have had some experience with SQL before.

I have a simple query that runs monthly which counts the number of active members and notifications sent per Organization for the month.

I have created a one-step job with SQL SERVER AGENT that runs the query on the 5th of the month and records the information for the previous month.

I have the output of the job going to a file named MonthlyReport.txt. This .txt file is then mailed to the client.

The client opens the file with Excel as default... this removes all the formatting. I recommended opening Excel and importing the document and this has temporarily resolved the issue.

However, there are TWO very big issues: 1) asking the client to import the file for formatting is very inconvenient. Importing the file myself will create a lot of overhead as there are several of these reports for multiple databases.

2) the .txt file includes the lines "MonthlyReport' : Step 1, 'Collect Data' : Began Executing 2015-03-17 12:39:58"------ This really messes up the formatting of the column headers

I am looking for other ways to resolve this task by exporting directly to EXCEL or a formatted .txt file

I have tried saving the output as MonthlyReport.csv but the problems still remain and this requires importing into excel

FYI-- My company is running Windows Server 2012 which has SSIS functionality but we also are running a few legacy Windows Server 2008 R2 and I need the solution to work on both servers so SSIS packages are not compatible with Windows Server 2008.

I am sorry for the longwinded response and appreciate all the time and help that the community is able to provide.

Dylan Haines
  • 75
  • 7
  • Would changing the file to a CSV work for you? – Zane Mar 17 '15 at 19:36
  • 1
    `so SSIS packages are not compatible with Windows Server 2008` Incorrect. Now, a 2012 SSIS Package will not execute on a SQL Server 2008 instance but it makes no matter what the Operating System is. That said, build your package on 2008. A 2008 package will run on SQL Server 2012 just fine – billinkc Mar 17 '15 at 19:54
  • wow thank you... Had no idea that it worked going forward but not back... Thanks! I guess I will have to look through an SSIS overview – Dylan Haines Mar 17 '15 at 19:56
  • If you're dead set on doing something outside SSIS, you can enable OLE automation of some such and generate Excel from within SQL Server itself but that seems an ugly approach – billinkc Mar 17 '15 at 19:56
  • @Zane when the .csv is opened directly with Excel the formatting still disappears so still have to use Excel Import file to resolve the issue – Dylan Haines Mar 17 '15 at 19:57
  • @billinkc So apparently we do not have Business Intelligence Development Studio in our environment... Not really in charge of the software more of a "Use what I Can situation". So any other ideas about the EXCEL is OLE automation my only other option? – Dylan Haines Mar 17 '15 at 20:11
  • 1
    The Business Intelligence Designer Studio is on the SQL Server 2008 installation media – billinkc Mar 17 '15 at 20:16

0 Answers0