如何使用SSIS包将大型数据集分割成多个Excel电子表格?

I'm facing a problem with an SSIS package.

我正面临一个SSIS包的问题。

• A query is executed to obtain some data from the DataBase (SQL Server 2008) (Data Flow Task executed)
• 执行查询以从数据库(SQL Server 2008)获得一些数据(数据流任务执行)
• Export the data extracted to an Excel 97-2003 spreadsheet (.xls) using Excel Destination
• 将提取的数据导出到Excel 97-2003电子表格(.xls)中，使用Excel目的地。

As most of you know the xls files are limited per sheet to 65,536 rows by 256 columns. So when the query extracts more than the records limit (65,536), the Excel Destination Step fails.

正如大多数人所知道的，xls文件被限制为每页65,536行、256列。因此，当查询提取超过记录限制(65,536)时，Excel目的地步骤失败。

I get the following error messages.

我得到以下错误消息。

Error: 0xC0202009 at Calidad VIDA, Excel Destination [82]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

错误:Calidad VIDA的0xC0202009, Excel Destination [82]: SSIS错误代码DTS_E_OLEDBERROR。发生了一个OLE DB错误。错误代码:0 x80004005。

Error: 0xC0209029 at Calidad VIDA, Excel Destination [82]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (93)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (93)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at Calidad VIDA, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (82) failed with error code 0xC0209029 while processing input "Excel Destination Input" (93). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

错误:Calidad VIDA的0xC0209029, Excel Destination [82]: SSIS错误码dts_e_induced transformedfailureonerror。“输入”Excel目的地输入”(93)“失败，因为发生了错误代码0xC020907B，而“输入”Excel目的地输入”(93)的“错误行配置”指定错误失败。在指定组件的指定对象上发生错误。在此之前，可能会出现错误消息，并提供有关失败的更多信息。错误:在Calidad VIDA, SSIS, 0xC0047022。管道:SSIS错误代码DTS_E_PROCESSINPUTFAILED。组件“Excel Destination”(82)的ProcessInput方法在处理输入“Excel Destination input”(93)时错误代码0xC0209029失败。被识别的组件从ProcessInput方法返回一个错误。错误是特定于组件的，但是错误是致命的，会导致数据流任务停止运行。在此之前，可能会出现错误消息，并提供有关失败的更多信息。

Error: 0xC02020C4 at Calidad VIDA, OLE DB Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

错误:在Calidad VIDA的0xC02020C4, OLE DB源[1]:尝试向数据流任务缓冲区添加一行失败，错误代码为0xC0047020。

Error: 0xC0047038 at Calidad VIDA, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

错误:在Calidad VIDA, SSIS, 0xC0047038。管道:SSIS错误代码DTS_E_PRIMEOUTPUTFAILED。组件“OLE DB源代码”(1)上的PrimeOutput方法返回错误代码0xc0202020c4。当管道引擎调用PrimeOutput()时，该组件返回一个失败代码。失败代码的含义由组件定义，但是错误是致命的，并且管道停止执行。在此之前，可能会出现错误消息，并提供有关失败的更多信息。

The file needs to be in that format, because the clients don't have newer versions. And they don't want to buy licenses. Does anyone know how to work around with this issue? I should use a Script task and make the excel by my own, or I should make a for each loop and create various excels woorkbooks?

该文件需要采用那种格式，因为客户端没有更新的版本。他们不想买许可证。有人知道如何处理这个问题吗?我应该使用脚本任务并自己制作excel，还是应该为每个循环创建一个for循环并创建各种优秀的woorkbooks?

3 个解决方案

#1

Here is one possible option that you can use to create Excel worksheets dynamically using the SSIS based on how many number of records you want to write per Excel sheet. This doesn't involve Script tasks. Following example describes how this can be achieved using Execute SQL Tasks, For Loop container and Data Flow Task. The example was created using SSIS 2008 R2.

这里有一个可能的选项，您可以使用SSIS根据每个Excel表要写入的记录数量动态创建Excel工作表。这并不涉及脚本任务。下面的示例描述如何使用Execute SQL任务(For Loop container和Data Flow Task)实现这一点。这个示例是使用SSIS 2008 R2创建的。

Step-by-step process:

循序渐进的过程:

1. In SQL Server database, run the scripts provided under SQL Scripts section. These scripts will create a table named dbo.SQLData and then will populate the table with multiplication data from 1 x 1 through 20 x 40, thereby creating 800 records. The script also creates a stored procedure named dbo.FetchData which will be used in the SSIS package.

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

2. On the SSIS package, create 9 variables as shown in screenshot #1. Following steps describe how each of these variables are configured.

   在SSIS包中，创建9个变量，如屏幕截图#1所示。下面的步骤描述了如何配置这些变量。

3. Set the variable ExcelSheetMaxRows with value 80. This variable represents the number of rows to write per Excel sheet. You can set it to value of your choice. In your case, this would be 65,535 (you might want to leave 1 row for header column names).

   将变量ExcelSheetMaxRows设置为80。这个变量表示每个Excel表要写入的行数。您可以将它设置为您所选择的值。在您的例子中，这将是65,535(您可能希望为标题列名保留一行)。

4. Set the variable SQLFetchTotalRows with value SELECT COUNT(Id) AS TotalRows FROM dbo.SQLData. This variable contains the query to fetch the total row count from the table.

   将变量SQLFetchTotalRows与值SELECT COUNT(Id)设置为来自dbo.SQLData的TotalRows。这个变量包含从表中获取总行数的查询。

5. Select the variable StartIndex and choose Properties by pressing F4. Set the property EvaluateAsExpression to True and the property Expression to the value (@[User::Loop] * @[User::ExcelSheetMaxRows]) + 1. Refer screenshot #2.

   选择变量StartIndex，按F4选择属性。将属性EvaluateAsExpression设置为True，将属性表达式设置为值(@[User::Loop] * @[User::::ExcelSheetMaxRows]) + 1。请参考截图# 2。

6. Select the variable EndIndex and choose Properties by pressing F4. Set the property EvaluateAsExpression to True and the property Expression to the value (@[User::Loop] + 1) * @[User::ExcelSheetMaxRows]. Refer screenshot #3.

   选择变量EndIndex并按F4选择属性。将属性EvaluateAsExpression设置为True，将属性表达式设置为值(@[User: Loop] + 1) * @[User:::ExcelSheetMaxRows]。请参考截图# 3。

7. Select the variable ExcelSheetName and choose Properties by pressing F4. Set the property EvaluateAsExpression to True and the property Expression to the value "Sheet" + (DT_WSTR,12) (@[User::Loop] + 1). Refer screenshot #4.

   选择变量ExcelSheetName，按F4选择属性。将属性EvaluateAsExpression设置为True，将属性表达式设置为“Sheet”+ (DT_WSTR,12) (@[User::::Loop] + 1)。

8. Select the variable SQLFetchData and choose Properties by pressing F4. Set the property EvaluateAsExpression to True and the property Expression to the value "EXEC dbo.FetchData " + (DT_WSTR, 15) @[User::StartIndex] + "," + (DT_WSTR, 15) @[User::EndIndex]. Refer screenshot #5.

   选择变量SQLFetchData并按F4选择属性。将属性评估表达式设置为True，并将属性表达式设置为“EXEC dbo”。FetchData " + (DT_WSTR, 15) @[User::StartIndex] + "，" + (DT_WSTR, 15) @[User::::EndIndex]。请参考截图# 5。

9. Select the variable ExcelTable and choose Properties by pressing F4. Set the property EvaluateAsExpression to True and the property Expression to the value provided under ExcelTable Variable Value section. Refer screenshot #6.

   选择变量ExcelTable并按F4选择属性。将属性评估表达式设置为True，将属性表达式设置为ExcelTable变量值部分提供的值。请参考截图# 6。

10. On the SSIS package's Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #7 and #8. This task will fetch the record count.

    在SSIS包的Control Flow选项卡上，放置一个Execute SQL任务并对其进行配置，如屏幕截图#7和#8所示。此任务将获取记录计数。

11. On the SSIS package's Control Flow tab, place a For Loop Container and configure it as shown in screenshot #9. Please note this is For Loop and not Foreach Loop. This loop will execute based on the number of records to display in each Excel sheet in conjunction with the total number of records found in the table.

    在SSIS包的控制流选项卡上，放置一个For循环容器并按照屏幕截图#9所示配置它。请注意这是For循环，不是Foreach循环。此循环将根据要在每个Excel表中显示的记录数量以及在表中找到的记录总数执行。

12. Create an Excel spreadsheet of Excel 97-2003 format containing .xls extension as shown in screenshot #10. I created the file in **C:\temp**

    创建Excel 97-2003格式的电子表格，其中包含.xls扩展，如截图#10所示。我在**C:\temp**中创建了这个文件

13. On the SSIS package's connection manager, create an OLE DB connection named SQLServer pointing to SQL Server and an Excel connection named Excel pointing to the newly created Excel file.

    在SSIS包的连接管理器上，创建一个名为SQLServer的OLE DB连接，指向SQLServer;创建一个名为Excel连接，指向新创建的Excel文件。

14. Click on the Excel connection and select Properties. Changes the property DelayValidation from False to True so that when we switch to using variable for sheet creation in Data Flow Task, we won't get any error messages. Refer screenshot #11.

    单击Excel连接并选择Properties。将属性DelayValidation从False改为True，这样当我们在数据流任务中切换到使用变量来创建表时，我们将不会收到任何错误消息。请参考截图# 11。

15. Inside the For Loop container, place an Execute SQL Task and configure it as shown in screenshot #12. This task will create Excel worksheets based on the requirements.

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

16. Inside the For Loop container, place a Data flow task. Once the tasks are configured, the Control Flow tab should look like as shown in screenshot #13.

    在For循环容器中，放置一个数据流任务。一旦任务被配置，控制流选项卡应该像屏幕截图#13所示。

17. Inside the Data Flow Task, place an OLE DB Source to read data from SQL Server using the stored procedure. Configure the OLE DB Source as shown in screenshots #14 and #15.

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

18. Inside the Data Flow Task, place an Excel Destination to insert the data into the Excel sheets. Configure the Excel destination as shown in screenshots #16 and #17.

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

19. Once the Data Flow Task is configured, it should look like as shown in screenshot #18.

    一旦数据流任务被配置，它应该看起来像屏幕截图#18所示。

20. Delete the Excel file that was created in step 12 because the package will automatically create the file when executed. If not deleted, the package will throw the exception that Sheet1 already exists. This example uses the path C:\temp\ and screenshot #19 shows there are no files in that path.

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

21. Screenshots #20 and #21 show the package execution inside Control Flow and Data Flow tasks.

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

22. Screenshot #22 shows that file ExcelData.xls has been created in the path C:\temp. Remember, earlier this path was empty. Since we had 800 rows in the table and we set the package variable ExcelSheetMaxRows to create 80 rows per sheet. Hence, the Excel file has 10 sheets. Refer screenshot #23.

    屏幕截图#22显示了文件ExcelData。已经在路径C:\temp中创建了xls。记住，前面的路径是空的。由于表中有800行，所以我们设置了package变量ExcelSheetMaxRows来为每个表创建80行。因此，Excel文件有10个表。请参考截图# 23。

23. NOTE: One thing that I haven't done in this example is to check if the file ExcelData.xls already exists in the path C:\temp. If it exists, then the file should be deleted before executing the tasks. This can be achieved by creating a variable that holds the Excel file path and use a File System Task to delete the file before the first Execute SQL Task is executed.

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

Hope that helps.

希望有帮助。

ExcelTable Variable Value:

ExcelTable变量值:

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

SQL Scripts:

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

Screenshot #1:

截图# 1:

Screenshot #2:

截图# 2:

Screenshot #3:

截图# 3:

Screenshot #4:

截图# 4:

Screenshot #5:

截图# 5:

Screenshot #6:

截图# 6:

Screenshot #7:

截图# 7:

Screenshot #8:

截图# 8:

Screenshot #9:

截图# 9:

Screenshot #10:

截图# 10:

Screenshot #11:

截图# 11:

Screenshot #12:

截图# 12:

Screenshot #13:

截图# 13:

Screenshot #14:

截图# 14:

Screenshot #15:

截图# 15:

Screenshot #16:

截图# 16:

Screenshot #17:

截图# 17:

Screenshot #18:

截图# 18:

Screenshot #19:

截图# 19:

Screenshot #20:

截图# 20:

Screenshot #21:

截图# 21:

Screenshot #22:

截图# 22:

Screenshot #23:

截图# 23:

#2

The stock excel rendering option allows for options to paginate onto separate tabs. If you force page breaks after the appropriate number of rows, you get a new tab for each "page" in your output. I don't have the settings I have used in the past for this, but I can look it up tomorrow if you need me to.

stock excel呈现选项允许在单独的选项卡上分页。如果在适当的行数之后强制分页，则会为输出中的每个“页面”获得一个新的选项卡。我没有以前用过的设置，但是如果你需要的话，我可以明天再查。

#3

Also worked for me was to use the SSIS package to export to a CSV file and then manually import the data into Excel. Note that this is not the same as "Open" in Excel, because that will also just stop at 65536 rows. Create a new xlsx file and click on "Data" -> "From Text". It will import and show all rows. Tested with 750,000 rows.

我还使用SSIS包将数据导出到CSV文件中，然后手工将数据导入到Excel中。注意，这与Excel中的“Open”不同，因为它也只会在65536行停止。创建一个新的xlsx文件并单击“Data”->“From Text”。它将导入并显示所有行。测试了750000行。

However, not sure if the csv -> xlsx conversion is easily scripted within an SSIS package. Most likely via a Script task using the Excel COM object.

但是，不确定csv -> xlsx转换是否容易在SSIS包中编写脚本。最可能的方法是使用Excel COM对象执行脚本任务。