Scheduled export to Excel and email

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Scheduled export to Excel and email

Author

Topic

Fromper
Starting Member

33 Posts

Posted - 2014-12-15 : 17:22:09

I'm pretty new to this side of SQL, so I'm not sure how to do what I need, and I was hoping someone could point me to some step by step instructions, since I'm sure they must exist somewhere already.

I wrote a report as a stored procedure, and I need a way to schedule it to run weekly and email the results automatically to a business user. Ideally, this would be as an Excel attachment on the email. Making it a .csv won't work because there are commas in the data. If I can just schedule it to create the export every week on our network, we can use non-SQL means to email it to the user.

So I tried googling how to do this. [url=http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/]This[/url] looked promising, but apparently, it only works if everything is 32 bit, and we've got a lot of 64 bit machines (not sure if it's all of them, but definitely the relevant ones for this).

Another possibility I found is sp_send_dbmail, but we don't have that configured to use, and I was unclear from what I read as to whether it could do an attachment in Excel format. I can probably figure out the configuration from the links I found, but it's not worth the effort until I'm sure it can handle Excel format, and how to tell it to do that. Anyone?

Of course, I also found lots of references to using SSIS, but I'm just not experienced enough to know how to do that, and I'm having trouble finding step by step instructions in the sea of links that I found. If I could use that to produce the export file every week on our network, like I said, we could use an outside process to do the emailing.

Any help would be appreciated!

Fromper
Starting Member

33 Posts

Posted - 2015-01-06 : 15:06:00

So I found these step by step instructions that seem to be exactly what I need: http://oakdome.com/programming/SSIS_DataTransform.php

But when I try to run the resulting SSIS, I get the error "cannot convert between unicode and non-unicode string data types". Googling this, I keep seeing the same solution over and over, which is some variation of "Add a Data Conversion transformation to your data flow to convert the incoming types to the required destination data types."

Edit: Wait, I may actually have this one figured out on my own.

Fromper
Starting Member

33 Posts

Posted - 2015-01-06 : 15:43:06

So the SSIS package runs, but it doesn't actually transfer my data to my Excel spreadsheet. Any ideas?

More importantly, does anyone ever actually read and respond to this forum? This thread is 3 weeks old, and I seem to be talking to myself.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-07 : 09:57:54

I would be inclined to write an app or use a script to do this.

If you are not a developer you should probably get to know Powershell.

eg The following shows how to dump a SQL query to an XLS.

http://www.sqlserver-dba.com/2013/05/sql-server-export-to-excel-with-powershell.html

You should easily find lots of examples on how to call a SP instead.

There are also lots of examples on how to email from Powershell.

Once you have written the script you can then schedule it from something like SQL Server Agent, Windows Task Scheduler etc.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2015-01-07 : 15:04:25

If you can get Database Mail setup and configured - it is quite easy to send a CSV file with a different delimiter. For example:


Declare @query varchar(max);
    Set @query = '
 Select col1 As [Sep=|' + char(13) + char(10) + 'Column1]'
      , col2
      , col3
   From dbo.YourTable';

Execute msdb.dbo.sp_send_dbmail
        @profile_name = 'Your DB Mail Profile'
      , @recipients = '{list of users}'
      , @subject = 'Your Subject'
      , @body = 'Some body'
      , @query = @query
      , @query_result_separator = '|'
      , @query_result_header = 1
      , @attach_query_result_as_file = 1
      , @query_attachment_filename = 'SomeFileName.csv'

There are other parameters you may need - like @execute_query_database, @copy_recipients, @from_address, @reply_to, etc...

When the users double-click on the file, Excel will open the file using the defined separator.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources