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.
我有一个任务,那就是我要将数据从MS Sql Server存储过程导出到不同工作表中的excel文件。(同一个excel文件中的不同工作表)格式如方向是横向,在Excel的A列的每个值之后的分页符表等
At last that excel file sent via email.
最后通过电子邮件发送excel文件。
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.
请建议哪种方法适合此任务。 SSRS,SSIS包或创建疼痛程序概念。请建议任何链接或任何示例来完成此任务。
Right now I am doing this task manually, So I need to complete this task by automation. Please help and suggest.
现在我手动完成这项任务,所以我需要通过自动化完成这项任务。请帮忙并提出建议。
4 个解决方案
#1
2
using SSIS, possible export data to excel
使用SSIS,可能的出口数据优秀
Source : OLEDB SOurce Destination : Execle detination
来源:OLEDB SOurce目的地:Execle detination
Once create and deployed the package , schedule in SQL Server agent.
创建并部署程序包后,在SQL Server代理程序中进行计划。
#2
1
You can take a look here
你可以看看这里
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.
本文使用批量复制程序(BCP)来创建CSV文件。尽管可以使用DTS或SSIS创建CSV文件,但使用BCP通常更简单,更高效。
I use master..sysobjects as an example table to extract.
我使用master..sysobjects作为示例表来提取。
Create a simple CSV file
创建一个简单的CSV文件
The simplest way to copy data from a database table to file is to use the basic BCP command:
将数据从数据库表复制到文件的最简单方法是使用基本BCP命令:
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 master..sysobjects out c:\ sysobjects.txt -c -t,-T -S用于创建CSV文件的BCP命令的基本格式如下:
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.
请注意,与DTS / SSIS一样,BCP是客户端实用程序,因此您需要提供连接信息。
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.
要在SQL服务器之间传输数据,而不是-c,请使用-n或-N作为本机数据格式(-N = Unicode)。这样更快,避免了数据转换问题。有关BCP命令的完整格式,请参阅之前的BOL链接。
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:
由于BCP是命令行实用程序,因此使用xp_cmdshell从T-SQL执行。在c:驱动器上创建一个名为BCP的目录并执行:
declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servernameexec master..xp_cmdshell @sql
#3
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:
SSRS可能是实现您所追求的最简单的方法。您可以使用分组将Excel输出分成单独的工作表,如下所示:
Reporting Services export to Excel with Multiple Worksheets
Reporting Services使用多个工作表导出到Excel
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
#4
1
SSRS supports export to multiple Excel worksheets. Here is a link, which explains how: http://technet.microsoft.com/en-us/library/dd255278.aspx
SSRS支持导出到多个Excel工作表。这是一个链接,解释了如何: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
另一种选择是使用第三方工具。它支持多种报告类型,包括SSRS和对存储过程的直接调用。您可以查看此视频的前几分钟,了解其运作方式: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.
该工具不是免费的,但允许您在大约一分钟内设置,运行或安排任务。此工具的一个优点是能够格式化excel文件,以便您可以将数据设置为导出为带有摘要和过滤器的Excel表格。您还可以通过不同方式显示数据:分组,透视等。该工具支持SSRS的数据驱动订阅。 @SQLDiver建议使用它来发送电子邮件,但数据驱动的订阅仅在SQLServer Enterprise中可用,这是昂贵的。如果您需要数据驱动的报告,此工具可能是企业版的廉价替代品。所以这取决于你的需求。如果您只有这样的任务,基于SSRS或SSIS的解决方案将更便宜,可能是更好的选择。如果您计划将来有多个任务,您需要特殊的格式,或者您需要数据驱动的子驱动,它可能值得检查上面提到的第三方工具。
#1
2
using SSIS, possible export data to excel
使用SSIS,可能的出口数据优秀
Source : OLEDB SOurce Destination : Execle detination
来源:OLEDB SOurce目的地:Execle detination
Once create and deployed the package , schedule in SQL Server agent.
创建并部署程序包后,在SQL Server代理程序中进行计划。
#2
1
You can take a look here
你可以看看这里
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.
本文使用批量复制程序(BCP)来创建CSV文件。尽管可以使用DTS或SSIS创建CSV文件,但使用BCP通常更简单,更高效。
I use master..sysobjects as an example table to extract.
我使用master..sysobjects作为示例表来提取。
Create a simple CSV file
创建一个简单的CSV文件
The simplest way to copy data from a database table to file is to use the basic BCP command:
将数据从数据库表复制到文件的最简单方法是使用基本BCP命令:
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 master..sysobjects out c:\ sysobjects.txt -c -t,-T -S用于创建CSV文件的BCP命令的基本格式如下:
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.
请注意,与DTS / SSIS一样,BCP是客户端实用程序,因此您需要提供连接信息。
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.
要在SQL服务器之间传输数据,而不是-c,请使用-n或-N作为本机数据格式(-N = Unicode)。这样更快,避免了数据转换问题。有关BCP命令的完整格式,请参阅之前的BOL链接。
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:
由于BCP是命令行实用程序,因此使用xp_cmdshell从T-SQL执行。在c:驱动器上创建一个名为BCP的目录并执行:
declare @sql varchar(8000)select @sql = 'bcp master..sysobjects out
c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servernameexec master..xp_cmdshell @sql
#3
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:
SSRS可能是实现您所追求的最简单的方法。您可以使用分组将Excel输出分成单独的工作表,如下所示:
Reporting Services export to Excel with Multiple Worksheets
Reporting Services使用多个工作表导出到Excel
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
#4
1
SSRS supports export to multiple Excel worksheets. Here is a link, which explains how: http://technet.microsoft.com/en-us/library/dd255278.aspx
SSRS支持导出到多个Excel工作表。这是一个链接,解释了如何: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
另一种选择是使用第三方工具。它支持多种报告类型,包括SSRS和对存储过程的直接调用。您可以查看此视频的前几分钟,了解其运作方式: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.
该工具不是免费的,但允许您在大约一分钟内设置,运行或安排任务。此工具的一个优点是能够格式化excel文件,以便您可以将数据设置为导出为带有摘要和过滤器的Excel表格。您还可以通过不同方式显示数据:分组,透视等。该工具支持SSRS的数据驱动订阅。 @SQLDiver建议使用它来发送电子邮件,但数据驱动的订阅仅在SQLServer Enterprise中可用,这是昂贵的。如果您需要数据驱动的报告,此工具可能是企业版的廉价替代品。所以这取决于你的需求。如果您只有这样的任务,基于SSRS或SSIS的解决方案将更便宜,可能是更好的选择。如果您计划将来有多个任务,您需要特殊的格式,或者您需要数据驱动的子驱动,它可能值得检查上面提到的第三方工具。