如何使用 SSIS 包将大型数据集拆分为多个 Excel 电子表格？

Question

我遇到了 SSIS 包的问题。

• 执行查询以从数据库（SQL Server 2008）（执行数据流任务）
• 使用 Excel 目标将提取的数据导出到 Excel 97-2003 电子表格 (.xls)

正如大多数人都知道的那样，xls 文件的每个工作表限制为 65,536 行 x 256 列。因此，当查询提取的记录数量超过记录限制 (65,536) 时，Excel 目标步骤将失败。

我收到以下错误消息。

错误：Calidad VIDA 处的 0xC0202009，Excel 目标 [82]：SSIS 错误代码 DTS_E_OLEDBERROR。发生 OLE DB 错误。错误代码：0x80004005。

错误：Calidad VIDA 处的 0xC0209029，Excel 目标 [82]：SSIS 错误代码 DTS_E_INDUCEDTRANSFORMFAILUREONERROR。 “输入“Excel 目标输入”(93)”失败，因为出现错误代码 0xC020907B，并且“输入“Excel 目标输入”(93)”上的错误行处理指定错误失败。指定组件的指定对象发生错误。在此之前可能会发布错误消息，其中包含有关失败的更多信息。错误：0xC0047022，位于 Calidad VIDA、SSIS.Pipeline：SSIS 错误代码 DTS_E_PROCESSINPUTFAILED。处理输入“Excel 目标输入”(93) 时，组件“Excel 目标”(82) 上的 ProcessInput 方法失败，错误代码为 0xC0209029。所识别的组件从 ProcessInput 方法返回错误。该错误特定于该组件，但该错误是致命的，将导致数据流任务停止运行。在此之前可能会发布错误消息，其中包含有关失败的更多信息。

错误：Calidad VIDA 处的 0xC02020C4，OLE DB 源 [1]：尝试向数据流任务缓冲区添加行失败，并显示错误代码 0xC0047020。

错误：Calidad VIDA、SSIS.Pipeline 处的 0xC0047038：SSIS 错误代码DTS_E_PRIMEOUTPUTFAILED。组件“OLE DB Source”(1) 上的 PrimeOutput 方法返回错误代码 0xC02020C4。当管道引擎调用 PrimeOutput() 时，组件返回失败代码。失败代码的含义由组件定义，但错误是致命的，管道停止执行。在此之前可能会发布错误消息，其中包含有关失败的更多信息。

该文件需要采用该格式，因为客户端没有更新的版本。他们不想购买许可证。有谁知道如何解决这个问题？ 我应该使用脚本任务并自己制作excel，或者我应该为每个循环创建一个foreach循环并创建各种excel工作簿？

Answer 1

这是一个可能的选项，您可以使用该选项根据您想要在每个 Excel 工作表中写入的记录数量，使用 SSIS 动态创建 Excel 工作表。这不涉及脚本任务。以下示例描述了如何使用执行 SQL 任务、For 循环容器和数据流任务来实现这一点。该示例是使用 SSIS 2008 R2 创建的。

分步过程：

1. 在 SQL Server 数据库中，运行 SQL 脚本 部分下提供的脚本。这些脚本将创建一个名为 dbo.SQLData 的表，然后使用从 1 x 1 到 20 x 40 的乘法数据填充该表，从而创建 800 条记录。该脚本还创建一个名为 dbo.FetchData 的存储过程，该存储过程将在 SSIS 包中使用。

2. 在 SSIS 包上，创建 9 变量，如屏幕截图 #1 所示。以下步骤描述了如何配置每个变量。

3. 将变量 ExcelSheetMaxRows 设置为值 80。此变量表示每个 Excel 工作表要写入的行数。您可以将其设置为您选择的值。在您的情况下，这将是 65,535（您可能希望为标题列名称保留 1 行）。

4. 将变量 SQLFetchTotalRows 设置为值 SELECT COUNT(Id) AS TotalRows FROM dbo.SQLData。此变量包含从表中获取总行数的查询。

5. 选择变量 StartIndex，然后按 F4 选择“属性”。将属性 EvaluateAsExpression 设置为 True，并将属性 Expression 设置为值 (@[User::Loop] * @[User: :ExcelSheetMaxRows]) + 1。请参阅屏幕截图 #2。

6. 选择变量 EndIndex，然后按 F4 选择“属性”。将属性 EvaluateAsExpression 设置为 True，并将属性 Expression 设置为值 (@[User::Loop] + 1) * @ [用户::ExcelSheetMaxRows]。请参阅屏幕截图 #3。

7. 选择变量 ExcelSheetName，然后按 F4 选择“属性”。将属性 EvaluateAsExpression 设置为 True，并将属性 Expression 设置为值 "Sheet" + (DT_WSTR,12) (@[User ::循环] + 1)。请参阅屏幕截图 #4。

8. 选择变量SQLFetchData，然后按F4选择“属性”。将属性 EvaluateAsExpression 设置为 True，并将属性 Expression 设置为值 "EXEC dbo.FetchData " + (DT_WSTR, 15) @ [用户::开始索引] + "," + (DT_WSTR, 15) @[用户::结束索引]。请参阅屏幕截图 #5。

9. 选择变量 ExcelTable，然后按 F4 选择“属性”。将属性 EvaluateAsExpression 设置为 True，将属性 Expression 设置为 ExcelTable 变量值 部分下提供的值。请参阅屏幕截图 #6。

10. 在 SSIS 包的“控制流”选项卡上，放置一个“执行 SQL 任务”并对其进行配置，如屏幕截图 #7 和 #8 所示。此任务将获取记录计数。

11. 在 SSIS 包的“控制流”选项卡上，放置一个 For 循环容器并对其进行配置，如屏幕截图 #9 所示。请注意，这是For 循环，而不是Foreach 循环。此循环将根据每个 Excel 工作表中显示的记录数以及表中找到的记录总数来执行。

12. 创建包含 .xls 扩展名的 Excel 97-2003 格式的 Excel 电子表格，如屏幕截图 #10 所示。我在 **C:\temp**

    创建

13. 在 SSIS 包的连接管理器上，创建一个名为 SQLServer 的 OLE DB 连接（指向 SQL Server）和一个名为 Excel 的 Excel 连接 指向新创建的 Excel 文件。

14. 单击 Excel 连接并选择“属性”。将属性 DelayValidation 从 False 更改为 True，以便当我们切换到使用变量在数据流任务中创建工作表时，我们不会收到任何错误消息。请参阅屏幕截图 #11。

15. 在 For 循环容器内，放置一个执行 SQL 任务并对其进行配置，如屏幕截图 #12 所示。此任务将根据要求创建 Excel 工作表。

16. 在 For 循环容器内，放置一个数据流任务。配置任务后，“控制流”选项卡应如屏幕截图 #13 所示。

17. 在数据流任务中，放置一个 OLE DB 源以使用存储过程从 SQL Server 读取数据。配置 OLE DB 源，如屏幕截图 #14 和 #15 所示。

18. 在数据流任务中，放置一个 Excel 目标以将数据插入到 Excel 工作表中。配置 Excel 目标，如屏幕截图 #16 和 #17 所示。

19. 配置数据流任务后，它应如屏幕截图 #18 所示。

20. 删除在步骤12中创建的 Excel 文件，因为包在执行时会自动创建该文件。如果不删除，包会抛出Sheet1已存在的异常。此示例使用路径 C:\temp\ ，屏幕截图 #19 显示该路径中没有文件。

21. 屏幕截图 #20 和 #21 显示控制流和数据流任务内的包执行。

22. 屏幕截图 #22 显示文件 ExcelData.xls 已在路径 C:\temp 中创建。请记住，早些时候这条路是空的。由于表中有 800 行，因此我们设置包变量 ExcelSheetMaxRows 来为每个工作表创建 80 行。因此，Excel 文件有 10 个工作表。请参阅屏幕截图 #23。

23. 注意： 在此示例中我没有做的一件事是检查文件 ExcelData.xls 是否已存在于路径 C:\temp 中。如果存在，则应在执行任务之前删除该文件。这可以通过创建一个保存 Excel 文件路径的变量并使用文件系统任务在执行第一个执行 SQL 任务之前删除该文件来实现。

希望有帮助。

ExcelTable 变量值：

"CREATE TABLE `" + @[User::ExcelSheetName] + "`(`Id` Long, `Number1` Long, `Number2` Long, `Value` Long)"

SQL 脚本：

--Create table

CREATE TABLE [dbo].[SQLData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Number1] [int] NOT NULL,
    [Number2] [int] NOT NULL,
    [Value] [int] NOT NULL,
CONSTRAINT [PK_Multiplication] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

--Populate table with data

SET NOCOUNT ON

DECLARE @OuterLoop INT
DECLARE @InnerLoop INT
SELECT @OuterLoop = 1

WHILE @OuterLoop <= 20 BEGIN

    SELECT @InnerLoop = 1   
    WHILE @InnerLoop <= 40 BEGIN

            INSERT INTO dbo.SQLData (Number1, Number2, Value)
            VALUES (@OuterLoop, @InnerLoop, @OuterLoop * @InnerLoop)

            SET @InnerLoop = @InnerLoop + 1
    END

    SET @OuterLoop = @OuterLoop + 1
END

SET NOCOUNT OFF

--Create stored procedure

CREATE PROCEDURE [dbo].[FetchData]
(
        @StartIndex INT
    ,   @EndIndex   INT
)
AS
BEGIN

    SELECT  Id
        ,   Number1
        ,   Number2
        ,   Value
    FROM    (
                SELECT  RANK() OVER(ORDER BY Id) AS RowNumber
                    ,   Id
                    ,   Number1
                    ,   Number2
                    ,   Value 
                FROM    dbo.SQLData
            ) T1
    WHERE   RowNumber BETWEEN @StartIndex AND @EndIndex
END
GO

屏幕截图 #1：

屏幕截图 #2：

屏幕截图 #3：

屏幕截图 #4：

屏幕截图 #5：

屏幕截图 # 6：

屏幕截图 #7：

屏幕截图 #8：

屏幕截图#9：

屏幕截图 #10：

屏幕截图 #11：

截屏#12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15：

屏幕截图 #16：

屏幕截图 #17：

屏幕截图 #18：

屏幕截图 #19：< /strong>

屏幕截图 #20：

屏幕截图 #21：

屏幕截图 #22：

屏幕截图＃23：

Answer 2

Stock Excel 渲染选项允许选项分页到单独的选项卡上。如果您在适当的行数之后强制分页，则输出中的每个“页面”都会有一个新选项卡。我没有过去使用过的设置，但如果您需要的话，我明天可以查一下。

Answer 3

对我来说也有用的是使用 SSIS 包导出到 CSV 文件，然后手动将数据导入到 Excel 中。请注意，这与 Excel 中的“打开”不同，因为这也只会停止在 65536 行处。新建一个xlsx文件，点击“数据”-> “来自文本”。它将导入并显示所有行。使用 750,000 行进行测试。

但是，不确定 csv 是否 -> xlsx 转换可以在 SSIS 包中轻松编写脚本。最有可能通过使用 Excel COM 对象的脚本任务。