批量导出数据 SSIS [英] Batch export data SSIS

问题描述

我需要从 SQL Server 中提取大量数据，并将这些数据插入到 Excel 文件中.

I have a requirement where I'm pulling large amounts of data from SQL Server and will have to insert this data into Excel files.

现在，每个 Excel 文件不得超过 1000 行.这些 excel 文件必须即时创建.

Now, each excel file must not have more than 1000 rows. These excel files will have to created on the fly.

请帮忙

推荐答案

我建议您将逻辑包装在循环容器中.从逻辑上讲，它看起来像这样:

I suggest you wrap your logic in a loop container. Logically, it would look like this:

• 计算准备导出到 excel 的行数.除以 1000，向上取整并设置为变量.(例如 5049 行 -> 6)

• 在新变量上有一个循环容器循环.

• Count the number of rows ready to export to excel. Divide by 1000, round up and set as a variable. (EX. 5049 rows -> 6)

• Have a loop container loop on the new variable.

• 在循环中，通过向查询添加行号(确保其有序...)来选择行的子集，例如 row >= (<variable>-1)*1000 AND行<(<变量>)*1000
• 您可以使用表达式来命名文件，并在末尾附加变量以提供唯一名称.前任.<代码>"<文件名>_" + DT_WSTR <变量>+ ".xls" 得到类似 dataset_1.xls, dataset_2.xls...

• Within the loop, select the subset of rows by adding a row number to the query (make sure its ordered...) with something like row >= (<variable>-1)*1000 AND row < (<variable>)*1000
• You can use an expression to name the file with the variable appended to the end to give a unique name. EX. "<filename>_" + DT_WSTR <variable> + ".xls" to get something like dataset_1.xls, dataset_2.xls...

这篇关于批量导出数据 SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！