65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SSIS 2005 和 SQL Server 数据动态生成 HTML/Excel/Word 文档文件

Venkat Chaitanya Kanumukula
starIconstarIconstarIcon
emptyStarIcon
starIcon
emptyStarIcon

3.90/5 (6投票s)

2012 年 1 月 4 日

CPOL

4分钟阅读

viewsIcon

80086

downloadIcon

1557

动态导出 SQL Server 表到 HTML/Excel/Word 文档文件。

引言

本文档主要面向熟悉 **SQL Server Business Intelligence** 的中高级 **SSIS** (SQL Server Integration Services) 用户。需要对脚本任务、ExecuteSql Task、参数和变量有基本了解，因为这并不是一个详细的教程，而是关于如何使用 **SSIS** 将数据库表对象导出到 **HTML/Excel/Word 文档**的实现描述。此程序包将帮助以简单高效的方式将数据迁移到格式良好的 **HTML** 文件中。本文的目的是通过提供一种概念，帮助用户使用 **SSIS** 创建包含 SQL Server 数据库表中所有数据的 **HTML/Excel/Word 文档**文件，并且无需任何模板、DLL 或大量编程即可获得更好的呈现效果和外观。

此程序包基本上是为了演示一种数据迁移方法，将 SQL Server 表导出为格式良好的静态 **HTML** 页面。这是一个简单的 DTSX 程序包，可以根据变量值的设置即时创建 HTML/Excel 文件。该程序包是使用 Visual Studio 2005 开发的。

以下是实现此目的的步骤演示。

dtsx 程序包应如下图所示。

背景

上述程序包使用了用于创建 HTML 文件的 **SP_MAKEWEBTASK** SQL Server 对象。此 SQL Server 对象负责所有 HTML 呈现和格式化，包括将表名作为标题。

有关 **SP_MAKEWEBTASK** 功能及其选项的更多信息，请参阅以下链接的在线文档 http://msdn.microsoft.com/en-us/library/aa238843(v=sql.80).aspx。

使用代码

**ServerOption** 必须开启，在运行程序包之前，需要重新配置 **SQL Server** 上的 'DATA ACCESS' 和 'Web Assistant Procedures' 选项。没有这些选项，程序包将失败。重要的是，数据库登录必须具有管理员权限才能成功执行程序包任务。这些选项目前在以下任务中设置。

[获取要处理的表列表] Execute SQL Task：查询用于检索所有表名。（根据您的需求自定义此查询。）

/********************************************************************** 
 CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is get the List of Tables from the Database.
 COMMENTS     : So that it can be used for creating objects in xls file via SSIS.
                Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition)
 SSIS TASK    : [Get List of Tables to Process] SSIS Execute SQL Task   
 **********************************************************************/

DECLARE @Server varchar(50) 
SET @Server = @@SERVERNAME 
-- This ServerOption Should be Turned on the Server. Not required every time.
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'

SELECT [TableName] = so.name 
                FROM sysobjects so, sysindexes si 
                WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
/*Uncomment the following line if you are using Excel 2003.*/
                --AND si.rows < 65335 
/*Uncomment the following code if you want to avoid the table names where you dont have data in it.*/
                --AND si.rows > 0
                GROUP BY so.name 
                ORDER BY 1 DESC 

-- Comment this later after executing. This is not required every time.
EXEC sp_configure 'Web Assistant Procedures', 1;
RECONFIGURE;

更改变量 DestFileType 值为 **HTM、HTML** 或 **XLS、XLSX** 或 **Doc、Docx**，如下图所示。更改数据库连接到 SQL Server 2000/2005，并指向您要导出的数据库。更改变量 DestExcelFilePath 值为 'C:\SSIS'，以便在所需位置创建文件。如果所有 表名和列名都遵循微软命名标准，程序包应该可以正常工作。

SSIS_Html1.jpg

[插入脚本生成] ScriptTask：此 VB.NET 脚本任务代码用于使用 SQL Server SP_MAKEWEBTASK 函数构建查询，该查询最终根据 DestFileType 变量的值在目标文件夹中创建文件，文件名分别为（**TableName.Html/TableName.xls/TableName.doc** 或其他）。

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()
        ' CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
        ' 
        '
        Dim excelFilePath As String = CStr(Dts.Variables("User::DestExcelFilePath").Value) 
               '+ CStr(Dts.Variables("DestExcelFileName").Value)
        Dim TableName, ExcelTable, FileType As String
        TableName = CStr(Dts.Variables("User::ExcelTable").Value)

        FileType = CStr(Dts.Variables("User::DestFileType").Value)

        If TableName.Length > 30 Then
            ExcelTable = Strings.Left(TableName, 31)
            'Excel sheet Name can only be 30 characters length.
        Else
            ExcelTable = TableName
        End If

        ' The sp_serveroption has to be set to TRUE on the Server for running SP_MAKEWEBTASK.
        ' To avoid runtime errors in column names replace '*' with column names in brackets[].

        Dim strCn As String = "EXEC  sp_makewebtask  " & _
        "@outputfile = '" + excelFilePath + ExcelTable + "." + FileType + "', " & _
        "@query = 'SELECT * FROM dbo.[" + TableName + "]', " & _
        "@colheaders =1,@FixedFont=0,@lastupdated=0," & _
        "@resultstitle= '" + TableName + " Table '"

        Dts.Variables("User::InsertScripter").Value = strCn
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

结论

上述步骤演示表明，可以使用 **SSIS 2005** 动态创建多个 **HTML/XLS/Word 文档**文件以及从 SQL Server 进行数据迁移。使用上述程序包导出**大型数据库**不是一个好主意，因为它可能会耗尽**系统内存**和其他**资源**。

SSIS_Html2.jpg

因此，可以得出结论，通过 **SSIS** 可以通过简单的步骤以最少的编程实现将数据库表对象导出到 **HTML/Excel/Word 文档**文件。

如果 DestFileType 变量值设置为 **XLS** 或 **XLSX**。应如下图所示。

SSIS_Html3.jpg

注意：当变量 DestFileType 值设置为 **XLS** 或 **XLSX** 时，以下脚本任务将创建 Excel 文件，但其内部包含 HTML 正文。

如果 DestFileType 变量值设置为 **HTM** 或 **HTML**。应如下图所示。

SSIS_Html4.jpg

如果 DestFileType 变量值设置为 **DOC** 或 **DOCX**。应如下图所示。

SSIS_Html5.jpg

注意：当变量 DestFileType 值设置为 **DOC** 或 **DOCX** 时，以下程序包将创建 Word 文档文件，但其内部包含 HTML 正文。

文件扩展名不限于上述提到的。如果您正在使用 Open Office，还有其他各种扩展可用。

上述图像仅作为示例，用于展示导出表到 HTML、Excel 和 Word 文档文件后，输出文件的格式和外观。

希望本文能达到其目的。欢迎任何建议或指正。

关注点

逆向工程、升级、转换、集成和数据迁移。

参考文献

http://msdn.microsoft.com/en-us/library/aa238843(v=sql.80).aspx

历史

2012 年 1 月 9 日

2012 年 1 月 4 日

© . All rights reserved.