使用 SSIS 2005 动态创建 Excel 文件、Excel 工作表以及从 SQL Server 导出数据

Venkat Chaitanya Kanumukula

4.63/5 (16投票s)

2011年12月20日

CPOL

6分钟阅读

368938

7144

动态将 SQL Server 表导出到 Excel 文件

下载 SSIS_Excel.zip - 60.25 KB

引言

本文主要面向熟悉 SQL Server Business Intelligence 的 SSIS (SQL Server Integration Services) 中高级用户。读者需要对脚本任务、执行 SQL 任务、参数和变量有基本的了解，因为这并非一篇入门教程，而是描述如何通过 SSIS 将数据库表对象导出到 Excel 对象，且无需任何模板和大量编程。此包将有助于以简单高效的方式迁移数据。此包对 SQL Server 2008 数据库用户也可能有用，可以帮助替换已弃用的 sp_makewebtask 功能来将数据导出到 Excel。

本文旨在为用户提供一个概念，即创建一个包含无限工作表的 Excel 文件，其中包含来自 SQL Sever 数据库的所有数据，从而无需使用 SSIS 的数据转换任务、映射以及预定义的列名、数据类型等。开发通用包的相同概念也可以类似地应用于将 SQL Server 数据迁移到不同数据库（如 Oracle、Teradata、MS-Access 等），这可以通过使用带有相关数据源提供程序的连接管理器，并借助 SSIS ETL 工具来实现。

这个通用的 DTSX 包主要是为了演示一种将 SQL Server 数据库表导出到具有多个工作表的 Excel 文件的数据迁移方法。在阅读了许多关于人们在通过 SSIS (SQL Server Integration Services) 从 SQL Server 2000/2005 动态生成 Excel 文件和工作表时遇到问题的博客后，我提供了这个简单的 DTSX 包，它可以即时创建一个 Excel 文件并将数据转储到工作表中。

目前，大多数微软 Dynamics ERP 软件产品（如 Navision、Axapta、Great Plains、Solomon、Concorde、Point of Sale、Retail Management System、Customer Relationship Management 等）也使用 SQL Server 和 Excel 进行数据迁移和报表制作。

对以下任务中的代码进行少量调整，可以帮助许多用户以最简单的方式实现他们的目标。

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

dtsx 包应如上图所示。该包使用 Visual Studio 2005 开发。

将数据库连接更改为 SQL Server 2000/2005，并指向您要导出的数据库（当前指向 master 数据库）。更改变量 DestExcelFilePath 的值，将其从 'C:\SSIS' 改为您希望创建文件的目标位置。

注意：如果数据库中的所有表名和列名都遵循微软的命名标准，则此包应能正常工作。

背景

Excel 文件将每个工作表视为一个表。Excel-95/2003 每个工作表最多只能生成 65335 行，如果是 Excel-95，最多只能有 255 个工作表。如果您使用的是 Excel 2007 及以上版本，则没有行数或工作表的限制。Excel 的早期版本存在一些限制。请阅读在线文档以获取更多信息。

以下是关于 Excel 不同版本差异和比较的一些链接。

http://www.add-ins.com/Excel%202003%20versus%202007.htm

http://technet.microsoft.com/en-us/library/cc179105(office.12).aspx

上述包使用 SQL Server 的 OPENROWSET 对象进行数据迁移。有关 OPENROWSET 功能及其选项的更多信息，请参阅以下链接的在线文档：http://msdn.microsoft.com/en-us/library/aa276850(v=sql.80).aspx

关于 OPENROWSET、SP_SERVEROPTION、SP_MAKEWEBTASK 以及诸如 'DATA ACCESS'、'SHOW ADVANCED OPTIONS'、'AD HOC DISTRIBUTED QUERIES' 等服务器选项的解释和使用超出了本文的范围。

Using the Code

在运行该包之前，需要在 SQL Server 上配置这些选项：'Show Advanced Options' 和 'Ad Hoc Distributed Queries'。没有这些选项，包将运行失败。而且，重要的是，数据库登录用户必须具有管理员权限才能成功执行包任务。如果您想运行此代码，最好与您的数据库管理员（DBA）确认您需要的权限。

/*-- This has to executed on the server before running the package.
  -- These Options are required to run the OPENROWSET Function.                                                      This has to be just executed once.*/

SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

[获取待处理的表列表] 执行 SQL 任务：此查询用于检索所有行数少于 65335 的表名。（请根据您的需求自定义此查询。）

/********************************************************************** 
 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 
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)
/*Comment the following line if you are using Excel 2007.*/
                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

[Excel 脚本生成器] 执行 SQL 任务：此任务中的查询会构建具有 Excel 所需数据类型的表结构。（这里已经处理了大多数 SQL 数据类型。如果您想添加任何其他特定的数据类型或不同的数据库，则需要修改此部分。）

 /********************************************************************** 
 CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for Generating a Script Table for Excel.
 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    : [Excel Script Generator] Execute SQL Task 
 **********************************************************************/
DECLARE @vsTableName VARCHAR(100)  
SET @vsTableName =?
DECLARE @vsSQL VARCHAR(8000)  
 
BEGIN  
  /* EXCEL TABLE SCRIPT GENERATOR 
     Handled Most of the regular SQL data types below. */
  SELECT @vsSQL = 'CREATE TABLE ' + '`' + @vsTableName + '`' + CHAR(10) + '(' + CHAR(10)  
  --SELECT @vsSQL = @vsSQL + '`' + RTRIM(sc.Name) + '` ' +  
  SELECT @vsSQL = @vsSQL + '[' + sc.Name + '] ' +  
  CASE WHEN st.Name IN ('nvarchar','ntext','text','varchar','varchar','char','nchar','xml','uniqueidentifier') THEN  'LongText'  
       WHEN st.Name IN ('tinyint','int','smallint','bigint','float','numeric','decimal','money','smallmoney','bit') THEN  'Long'
       WHEN st.Name IN ('date','datetime','timestamp') THEN  'datetime'
  ELSE ' ' END + ',' + CHAR(10) 
  FROM sysobjects so  
  JOIN syscolumns sc ON sc.id = so.id  
  JOIN systypes st ON st.xusertype = sc.xusertype  
  WHERE so.name = @vsTableName 
  /* Avoiding the following data types here... */
  AND st.Name not in ( 'image','sysname','binary','varbinary','xml','uniqueidentifier') 
  ORDER BY  
  sc.ColID  
  
  SELECT SUBSTRING(@vsSQL,1,LEN(@vsSQL) - 2) + CHAR(10) + ')'  AS ExcelTableName   
 END

[在 Excel 中创建表] 执行 SQL 任务：上述任务生成的创建表脚本查询被传递到此任务，该任务通过 Excel 连接管理器在 Excel 文件中执行，以创建具有 Excel 所需表结构和数据类型的工作表。目标文件名称为 (DynamicExcelFileDDMMYYYY.xls)。此任务基本上是创建 Excel 目标文件中所有工作表的关键。

[获取列名] 执行 SQL 任务：此任务构建列名并将其传递给下面的 [插入脚本生成] 脚本任务，该脚本任务在 OPENROWSET 查询中使用。

/********************************************************************** 
 CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for Generating Column names.
 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)
 MODIFIED BY  : VENKAT CHAITANYA KANUMUKULA  
 **********************************************************************/

DECLARE @SRCOBJECT varchar(8000)
SET @SRCOBJECT=?
DECLARE @SRCOBJECT1 varchar(8000)
SET @SRCOBJECT1 = SubString(@SRCOBJECT,CHARINDEX ('(',@SRCOBJECT)+ 1 , CHARINDEX (')',@SRCOBJECT) - 1)

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'LongText','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1 ,'Long','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'DateTime','')

SET @SRCOBJECT1= Replace(@SRCOBJECT1,')','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'`','')

SELECT @SRCOBJECT1

[插入脚本生成] 脚本任务：此脚本任务中的 vb.net 代码用于使用 SQL Server OPENROWSET 函数构建一个查询，最终将数据插入到 Excel 工作表中。该查询通过一个变量传递给执行 SQL 任务，并通过 Excel 连接管理器在 Excel 文件上执行。

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

Public Class ScriptMain

    Public Sub Main()
        ' CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
        ' Insert Script Generation

        '
        Dim excelFilePath As String = CStr(Dts.Variables("User::DestExcelFilePath").Value) + CStr(Dts.Variables("DestExcelFileName").Value)
        Dim TableName, ExcelTable As String
        TableName = CStr(Dts.Variables("User::ExcelTable").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
       'Retrieve the ExcelColumnNames from the Variable and build the String Here.
        Dim ColumnNames As String = CStr(Dts.Variables("User::ExcelColumns").Value)
        Dim strCn As String = " Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
                "'Excel 8.0;Database=" + excelFilePath + "','SELECT " + ColumnNames + " FROM [" + ExcelTable + "$]') SELECT " + ColumnNames + " FROM [" + TableName + "]"

        'Uncomment the following message to check the query.
        'MsgBox(strCn)
        Dts.Variables("User::InsertScripter").Value = strCn
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

结论

上述步骤的演示表明，可以使用 SSIS 动态地创建一个包含多个工作表的 Excel 文件，并从 SQL Server 迁移数据。

因此可以得出结论，通过 SSIS 可以实现将数据库表对象导出到一个具有多个工作表的 Excel 文件中，而无需任何模板、DLL 和大量编程。

本文旨在演示使用 SSIS 开发一个通用包来在 Excel 文件中创建工作表和表结构的概念，并帮助理解 Excel 的功能。同样的概念也可以在将 SQL Server 数据迁移到不同数据库时应用。使用上述包导出庞大的数据库不是一个好主意，因为它可能会耗尽系统内存和其他资源。

由于 Web 助手功能（如 SP_MAKEWEBTASK，它有助于将数据对象导出为 html、excel、word 等）在 SQL Server 2008 中已不再可用。此包对于 SQL Server 2008 数据库的用户也可能有用，可以帮助替换已弃用的 sp_makewebtask 以将数据导出到 Excel 文件。

关于创建和处理 Excel 文件，包括格式化单元格、插入公式和操作 Excel 对象的文章有很多，这些都可以通过各种编程语言实现。但本文是为那些了解其优点和局限性的 SQL Server Integration Services 用户而写的。