将 SQL Server 脚本的输出导出到 Excel (SSIS)

• 下载 SQLScriptToExcel.zip - 539 KB

引言

我想解决的问题是，用户每个月有超过 20 个 Excel 文件需要生成。他们来找我寻求自动化解决方案。所有 Excel 文件都是使用不同的查询生成的，这些查询以 .sql 文件的形式存储。
一位同事 DBA 尝试使用导入/导出向导来解决这个问题，并将所有 SSIS 包单独存储。但我认为这种方法维护起来很麻烦。而且，随着时间的推移，查询数量可能会增加，最终你会发现维护起来很困难，因为每个 SSIS 包都需要单独维护。

我想要的解决方案应该需要最少的维护，并且易于添加新报表或更改现有报表。它应该能够处理“任何”SQL 文件并为其生成 Excel 文件。我选择使用 SSIS 是因为它具有灵活性和可定制性。SSIS 包大致执行以下步骤：

• 获取 SQL 脚本文件的内容。
• 从脚本代码创建一个临时表（用于确定输出的结构）。
• 生成并执行一个用于创建 Excel 工作表的脚本。
• 动态生成一个临时包，用于执行 SQL 脚本并将 Excel 文件作为输出。
• 执行并删除动态包。

SQL 文件先决条件

SQL 文件中的查询必须满足以下条件：

• 选定的列必须命名唯一。
• 列名只能包含字母数字字符和空格。
• 它必须返回单个结果集。

背景

SSIS 中的问题在于，无法简单地将 OLE DB 源用于查询文件和 Excel 目标。它还必须有一个数据转换组件，用于指定源中所有非 Unicode（仅限字符串）的列，并将它们转换为 Unicode。由于我们不知道 SQL 脚本文件的输出结构，因此必须找到解决方案。

我在网上搜索了是否有解决方案可以解决这个问题，但没有找到一个好的、可行的解决方案。
我找到的解决方案中有一些可用的部分，但没有一个能完全解决我的问题。

我以这篇文章为指导，动态生成 SSIS 包：API 示例 - 数据转换。

SSIS 包说明

这是 SSIS 包的控制流。

下面您将找到数据流中每个组件的说明。

1 获取 SQL 脚本文件内容

通过连接管理器 SQLScript 获取 SQL 脚本文件的内容，并将内容放入变量 SQLCommand 中。
生成一个脚本来创建一个临时表，该表包含 SQL 脚本将生成的列，并将脚本放入变量 TempTableCommand 中。

2 生成 Excel 创建脚本

此脚本根据 SQLScript 文件中的查询为 Excel 生成 CREATE TABLE 脚本。要提取查询的列和列类型，会创建一个带有 OLE DB 源的内存中包。

3 删除 Excel 输出文件

删除指定 ConMgr DestinationExcel 中的 Excel 输出文件。此步骤将在包开始执行时与步骤 1 一起执行。

4 创建 Excel 输出文件

执行步骤 2 中生成的脚本。
结果是创建了一个新的 Excel 文件，其列名在第一个工作表中具有正确的类型。

5 动态生成包

生成一个带有数据流任务的新包。在数据流任务中，会添加一个 OLE DB 源、一个数据转换和一个 Excel 目标组件。数据转换组件执行非 Unicode 列到 Unicode 列的转换。
包保存在包参数 PackagePath 指定的位置。

6 执行动态包

执行步骤 5 中生成的包。如果 SQL 脚本生成输出，结果是将 Excel 工作表填充数据。

7 删除动态包

删除步骤 5 中生成的包。

连接管理器

连接管理器	描述
DestinationExcel	Excel 目标工作表（动态，基于包参数 DestinationExcel）。
DynamicPackage	动态包的位置和名称（仅在步骤 7 和 9 中使用）。
SourceDatabase	运行 SQLScript 的数据库（动态，基于包参数 SourceDatabase）。
SQLScript	SQL 脚本的位置和名称（动态，基于包参数 SQLScript）。
SQLScriptToExcel.log	包日志连接（日志记录到 CSV 文件）。

代码解释

在本章中，我将解释最重要的代码。它更多的是结构概述，而不是对所有使用方法的完整描述。

1 获取 SQL 脚本文件内容

Main 方法包含脚本的逻辑。它执行以下步骤：

• 获取脚本文件的内容。如果找不到脚本文件或脚本文件为空，则返回 ApplicationException。
• Execute 方法

GetTempTableName()

此方法生成一个带有 GUID 的临时表名（方法）。这将防止当您并行多次执行包时，这些执行生成相同的临时表。

• SQLCommand 参数用 SQL 脚本文件中的原始 SQL 语句填充。

这是 Main 方法的完整代码。

        public void Main()
        {
            byte[] emptyBytes = new byte[0];

            try
            {
                // Get content of SQL file that is specified in the SQLFile connection manager.
                string SqlFileName = Dts.Connections["SQLScript"].ConnectionString;
                string sqlCommand = System.IO.File.ReadAllText(SqlFileName);
                if (sqlCommand == null
                    || sqlCommand == String.Empty)
                {
                    Dts.Log("The SQL script file cannot be read or is empty.", 0, emptyBytes);
                    throw new ApplicationException("The SQL script file cannot be read or is empty.");
                }

                // Determine temporary package name.
                string tempPackageName = GetTempPackageName();

                Dts.Variables["SQLCommand"].Value = sqlCommand;

                // Everything OK, exit the script with success.
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            // In case of an exception, exit the script with failure.
            catch (System.Exception ex)
            {
                Dts.Log(String.Format("Script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

3 生成 Excel 创建脚本

这是一个 C# 脚本，用于生成 Excel CREATE TABLE 脚本。使用一个带有 OLE DB 源（指向 SQL 脚本文件）的内存中包来确定列和列类型。

        public void Main()
        {
            byte[] emptyBytes = new byte[0];
            try
            {
                // Create package and add a DataFlow task to it.
                Package package = new Package();
                MainPipe pipeline = AddDataFlowTask(package);

                // Add connection manager.
                ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

                // Add package variables.
                AddPackageVariables(package);

                // Add the OLEDB source.
                IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);

                // Generate Excel CREATE TABLE script.
                GenerateExcelCreateScript(oleDbSource);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            // In case of an exception, exit the script with failure.
            catch (System.Exception ex)
            {
                Dts.Log(String.Format("Generate Excel create script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

7 动态生成包

Main 方法包含用于生成动态包的脚本逻辑。
它执行以下步骤：

• 创建一个新包并向其中添加一个数据流任务。

Package package = new Package();
MainPipe pipeline = AddDataFlowTask(package);

• 为 OLE DB 源和 Excel 目标添加连接管理器。

ConnectionManager excelConnection = AddConnectionManagerExcel(package);
ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

• 将 SQLCommand 变量作为包变量添加到动态包中。

AddPackageVariables(package);

• 在这些组件之间添加 OLE DB 源、Excel 目标和转换。

IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);
                IDTSComponentMetaData100 excelDestination = AddDestinationExcel(pipeline, excelConnection);
                IDTSComponentMetaData100 transform = AddTransform(pipeline, oleDbSource);

• 配置转换（为非 Unicode 列添加到 Unicode 列的转换）。
• 配置 Excel 目标（从转换中获取 Unicode 列，并从 OLE DB 源中获取其余列）。

ConfigureTransform(pipeline, oleDbSource, excelDestination, transform);
ConfigureExcelDestination(pipeline, oleDbSource, excelDestination, transform);

• 保存包并将任务执行结果设置为“成功”。

SavePackage(package);
Dts.TaskResult = (int)ScriptResults.Success;

• 发生错误时，会向日志（通过连接管理器 SQLScriptToExcel.log）写入一条消息，并将任务执行结果设置为“失败”。

这是 Main 方法的完整代码。

public void Main()
{
    byte[] emptyBytes = new byte[0];
    try
    {
        // Create package and add a DataFlow task to it.
        Package package = new Package();
        MainPipe pipeline = AddDataFlowTask(package);

        // Add connection managers.
        ConnectionManager excelConnection = AddConnectionManagerExcel(package);
        ConnectionManager oleDbConnection = AddConnectionManagerOleDb(package);

        // Add package variables.
        AddPackageVariables(package);

        // Add source, destination and transformation.
        IDTSComponentMetaData100 oleDbSource = AddSourceOleDb(pipeline, oleDbConnection);
        IDTSComponentMetaData100 excelDestination = AddDestinationExcel(pipeline, excelConnection);
        IDTSComponentMetaData100 transform = AddTransform(pipeline, oleDbSource);

        // Configure the transform and the Excel destination.
        ConfigureTransform(pipeline, oleDbSource, excelDestination, transform);
        ConfigureExcelDestination(pipeline, oleDbSource, excelDestination, transform);

        // Save the package and exit the script with success.
        SavePackage(package);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    // In case of an exception, exit the script with failure.
    catch (System.Exception ex)
    {
        Dts.Log(String.Format("Script ended in error:\r\n{0}", ex.Message), 0, emptyBytes);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

部署

要部署包，请在 Visual Studio 中打开 SQLScriptToExcel 项目。右键单击项目并启动部署向导。我建议将包部署到：/SSISDB/SQLScriptToExcel/SQLScriptToExcel。

要从 SSIS 数据库服务器运行 SQL 脚本，您需要一个环境。创建此环境的说明可在 SQLScriptToExcel.zip 下载文件中的 Word 文档中找到。

历史

日期	更改
04-11-2015	First version.
19-11-2015	移除了使用临时表来确定列名和类型。 添加了一个带有 OLE DB 源的内存中包来确定列名和类型。

未来更改

目前没有计划。