2

I have one task, That is I want to export data from MS Sql Server store procedure to excel file in different sheets.(different sheets in same excel file) with formatting like orientation is landscape , page break after each value of column A of excel sheet, etc.

At last that excel file sent via email.

Please suggest which method is good for this task. SSRS, SSIS package or create Sore procedure concept. please suggest any link or any example to complete this task.

Right now I am doing this task manually, So I need to complete this task by automation. Please help and suggest.

hardik rawal
  • 117
  • 1
  • 2
  • 18

4 Answers4

2

using SSIS, possible export data to excel

Source : OLEDB SOurce Destination : Execle detination

Once create and deployed the package , schedule in SQL Server agent.

Ezhil Arasan
  • 450
  • 3
  • 5
1

You can take a look here

https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

This article use Bulk Copy Program (BCP) to create CSV files. Although it is possible to create a CSV file using DTS or SSIS, using BCP is often simpler and more efficient.

I use master..sysobjects as an example table to extract.

Create a simple CSV file

The simplest way to copy data from a database table to file is to use the basic BCP command:

BCP master..sysobjects out c:\sysobjects.txt -c -t, -T –S The basic format for the BCP command for creating a CSV file is as follows:

BCP out

The switches used here are:

-c Output in ASCII with the default field terminator (tab) and row terminator (crlf)
-t override the field terminator with ","
-T use a trusted connection. Note that U –P may be used for username/password
-S connect to this server to execute the command

Note that, like DTS/SSIS, BCP is a client utility, hence you need to supply the connection information.

For transfer of data between SQL servers, in place of –c, use –n or -N for native data format (-N = Unicode). This is much faster and avoids data conversion problems. Please refer to the previous BOL link for the complete format of the BCP command.

As BCP is a command line utility it is executed from T-SQL using xp_cmdshell. Create a directory called BCP on your c: drive and execute:

declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out 
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servernameexec master..xp_cmdshell @sql
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
1

SSRS is probably the simplest way to achieve what you are after. You would use grouping to divide the Excel output into separate sheets as detailed here:

Reporting Services export to Excel with Multiple Worksheets

Once you have set up the report, you can set up a data-driven subscription, which will send the file out at regular intervals according to a predetermined schedule:

http://msdn.microsoft.com/en-GB/library/ms159150(v=sql.105).aspx

Community
  • 1
  • 1
SQLDiver
  • 1,948
  • 2
  • 11
  • 14
1

SSRS supports export to multiple Excel worksheets. Here is a link, which explains how: http://technet.microsoft.com/en-us/library/dd255278.aspx

You can also create a standard subscription to send an email if you know the email address and it should not be retrieved from the database during the task.

Another option is to use a 3rd party tool. It supports multiple report types including SSRS and direct calls to stored procedures. You can check the first few minutes of this video to see how it works: http://www.r-tag.com/Pages/Preview_Demo.aspx

The tool is NOT free , but will allow you to set , run or schedule the task in about a minute. One of the advantages for this tool is the ability to format the excel file so you can set for example your data to be exported as an Excel table with summaries and filter. You can also present the data in different ways: grouped, pivoted etc. The tool supports data driven subscriptions for SSRS. @SQLDiver suggested to use this to send emails , but data driven subscription is available just in SQLServer Enterprise, which is pricey. This tool might be a cheap alternative for Enterprise edition if you need data-driven reports. So it depends on your needs. If you have just one task like that, a solution based on SSRS or SSIS will be cheaper and probably a better choice. If you are planning to have multiple tasks in the future, you need special formatting or you need data-driven subsriptions it might worth to check for 3rd party tools lie the one mentioned above.

Lan
  • 1,335
  • 1
  • 9
  • 14