如何使用 SSIS 包将表数据拆分为单独的命名 Excel 文件?

On the SSIS package's Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #4 and #5. This task will fetch the unique Site names.

在 SSIS 包的控制流"选项卡上，在执行 SQL 任务"之后放置一个 Foreach 循环容器，并配置 Foreach 循环容器，如屏幕截图 #6和 #7 这个循环将遍历结果集并将每个站点读入一个变量.然后，此变量用于提供 Excel 文件的名称以及即将添加的数据流任务中存储过程的参数.

On the SSIS package's Control Flow tab, place a Foreach Loop container after the Execute SQL Task and configure the Foreach Loop container as shown in screenshots #6 and #7 This loop will loop through the result set and will read each site into a variable. This variable is then used to provide the name for Excel file as well as the parameter to the stored procedure in a Data Flow Task that will be added shortly.

在 Foreach Loop 容器内，放置一个 Execute SQL Task，然后放置一个 Data Flow Task.此时，控制流"选项卡应如屏幕截图 #8 所示.您的软件包此时可能会显示错误，我们将在接下来的步骤中尽快修复该问题.我们将配置数据流任务，然后返回到 Foreach 循环容器中的执行 SQL 任务.

Inside the Foreach Loop container, place an Execute SQL Task and then place a Data Flow Task. At this point, the Control Flow tab should look like as shown in screenshot #8. Your package might show errors at this point, we will fix that soon in the next steps. We will configure the Data Flow Task and then will get back to Execute SQL Task within the Foreach Loop container.

在数据流任务中，放置一个 OLE DB Source 并按照屏幕截图 #9 和 #11 中所示进行配置.这将根据给定的站点从表中获取数据.点击参数...按钮设置查询参数.

Inside the Data Flow Task, place an OLE DB Source and configure it as shown in screenshots #9 and #11. This will fetch the data from table based on a given site. Click on the Parameters... button to set the query parameters.

如果表字段数据类型是VARCHAR，那么我们需要将其转换为NVARCHAR(unicode格式)，否则不需要这一步.在数据流任务中，在 OLE DB 源之后放置一个 Data Conversion 转换并按屏幕截图 #12 中所示进行配置.

If the table field datatypes are in VARCHAR, then we need to convert it into NVARCHAR (unicode format), otherwise this step is not needed. In the Data Flow task, place a Data Conversion transformation after the OLE DB Source and configure it as shown in screenshot #12.

接下来，在数据流任务中，放置一个 Excel 目标，单击第一个新建...按钮，如屏幕截图 #13 所示.

Next, inside the Data Flow Task, place an Excel Destination, click on the first New... button as shown in screenshot #13.

在 Excel 连接管理器对话框中，提供 Excel 文件路径并单击确定.请参阅屏幕截图 #14.返回 Excel Destination，单击第二个 New... 按钮，如屏幕截图 #15 所示.在创建表"对话框中，确保脚本如屏幕截图 #16 所示，然后单击确定".当出现屏幕截图 #17 中所示的警告时，单击确定".从Excel 工作表名称 下拉列表中选择值Template，如屏幕截图#18 所示.如屏幕截图 #19 所示配置列.

On the Excel Connection Manager dialog, provide an Excel File Path and click OK. Refer screenshot #14. Back in the Excel Destination, click on the second New... button as shown in screenshot #15. On the Create Table dialog, make sure the script is as shown in screenshot #16 and click OK. When displayed with a warning as shown in screenshot #17, click OK. Select the value Template from the Name of the Excel sheet dropdown as shown in screenshot #18. Configure the columns as shown in screenshot #19.

在 SSIS 包的连接管理器上，选择新创建的 Excel 连接管理器，然后按 F4 查看属性.将 Name 属性值更改为 Excel.将 DelayValidation 更改为 True，这样如果文件 Template.xls 不存在，您就不会收到错误消息.使用值 @[USer::ExcelFilePath] 设置 ServerName 表达式.请参阅屏幕截图 #20.注意: 应已在路径 C: empTemplate.xls 中创建 Excel 文件.您可能希望保存它，以便在将来的设计更改期间不会遇到.如果文件被删除，您仍然可以重新创建它.

On the SSIS package's connection manager, select the newly created Excel connection manager and press F4 to view the properties. Change the Name property value to Excel. Change DelayValidation to True so that if the file Template.xls doesn't exist, you don't get an error message. Set the ServerName Expression with value @[USer::ExcelFilePath]. Refer screenshot #20. NOTE: An Excel file should have been created in the path C: empTemplate.xls. You might want to save it so that you don't encounter during future design changes. You can still recreate it if the file is deleted.

返回 Excel Destination 并按屏幕截图 #21 中所示对其进行配置.配置数据流任务后，它应该如屏幕截图 #22 所示.

Go back to the Excel Destination and configure it as shown in screenshot #21. Once the Data Flow Task is configured, it should look like as shown in screenshot #22.

返回控制流"选项卡，在 Foreach 循环容器中配置执行 SQL 任务"，如屏幕截图 #23 所示.此任务将为每个站点名称创建新的 Excel 电子表格.

Back in Control Flow tab, configure the Execute SQL Task within the Foreach Loop container as shown in screenshot #23. This task will create new Excel spreadsheets for each Site name.

屏幕截图 #24 显示了文件夹 c: emp 在包执行之前 中的内容.

Screenshot #24 shows contents in folder c: emp before the package execution.

屏幕截图 #25 和 #26 显示了包的执行情况.

Screenshots #25 and #26 show the package execution.

屏幕截图 #27 显示文件夹 c: emp 包执行后的内容.

Screenshot #27 shows contents in folder c: emp after the package execution.

屏幕截图 #28 和 #29 显示了新创建的 Excel 电子表格 North.xls 和 South.xls 的内容.两个工作表都包含各自同名站点的数据.

Screenshots #28 and #29 show the contents of the newly created Excel spreadsheets North.xls and South.xls. Both the sheets contain the data respective the sites of the same name.

希望有所帮助.

ExcelSheet 变量值:

CREATE TABLE `Template` (`Id` Long, `FirstName` LongText, `LastName` LongText)

SQL 脚本:

CREATE TABLE [dbo].[SourceData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Site] [varchar](50) NOT NULL,
    [FirstName] [varchar](40) NOT NULL,
    [LastName] [varchar](40) NOT NULL,
CONSTRAINT [PK_SourceData] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceData (Site, FirstName, LastName) VALUES
    ('North', 'Jim', 'Smith'),
    ('North', 'Tim', 'Johnson'),
    ('North', 'Sachin', 'Tendulkar'),
    ('South', 'Horatio', 'Alger'),
    ('South', 'Jimi', 'Hendrix'),
    ('South', 'Bugs', 'Bunny');
GO

CREATE PROCEDURE dbo.GetSiteData
(
    @Site   VARCHAR(50)
)
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT  Id 
        ,   FirstName
        ,   LastName
    FROM    dbo.SourceData
    WHERE   Site = @Site
END 
GO

屏幕截图 #1:

屏幕截图 #2:

屏幕截图 #3:

屏幕截图 #4:

屏幕截图 #5:

截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15:

屏幕截图 #16:

屏幕截图 #17:

屏幕截图 #18:

屏幕截图 #19:

屏幕截图 #20:

屏幕截图 #21:

屏幕截图 #22:

屏幕截图 #23:

屏幕截图 #24:

屏幕截图 #25:

屏幕截图 #26:

屏幕截图 #27:

屏幕截图 #28:

屏幕截图 #29:

这篇关于如何使用 SSIS 包将表数据拆分为单独的命名 Excel 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！