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

2021-12-30 00:00:00 excel sql-server ssis

我正在处理来自 SQL Server 的一组数据，我想将这些数据放入一组 Excel 文件中.此任务需要自动运行才能按月运行.数据看起来像

I'm working with a set of data from SQL Server that I'd like to get into a group of Excel files. This task needs to be automated to run on a monthly basis. The data looks like

Site    ID      FirstName   LastName
------  ------- ---------   ---------
North   111     Jim         Smith
North   112     Tim         Johnson
North   113     Sachin      Tedulkar
South   201     Horatio     Alger
South   205     Jimi        Hendrix
South   215     Bugs        Bunny

我希望结果看起来像

In Excel file named North.xls

ID      FirstName   LastName
111     Jim         Smith
112     Tim         Johnson
113     Sachin      Tedulkar

In Excel file named South.xls

ID      FirstName   LastName
201     Horatio     Alger
205     Jimi        Hendrix
215     Bugs        Bunny

我想要拆分的站点列中有 70 到 100 个值.我正在使用 SSIS 来执行此任务，但是在使用 OLE DB 源任务从 SQL Server 提取数据后，我陷入了困境.接下来应该怎么做?如果使用其他工具有更简单的方法可以做到这一点，我也愿意这样做.

There are between 70 and 100 values in the Site column that I'd like to split upon. I'm using SSIS to perform this task, but I'm getting stuck after I've pulled the data from SQL Server with a OLE DB Source task. What should come next? If there is an easier way to do this using other tools I'm open to that too.

推荐答案

现在您可能已经找到了问题的答案.这适用于可能偶然发现此问题的其他用户.以下示例显示了如何为可能存在的任意数量的站点动态实现这一点.该示例是使用 SSIS 2008 R2 和 SQL Server 2008 R2 数据库创建的.

You have probably found an answer to your question by now. This is for other users who might stumble upon this question. Following example shows how this can be achieved dynamically for any number of Sites that might be present. The example was created using SSIS 2008 R2 with SQL Server 2008 R2 database.

分步过程:

  1. 执行 SQL Server 数据库中 SQL 脚本部分下给出的脚本，以创建名为 dbo.Source 的表并填充数据(类似于问题中给出的数据).它还创建了一个名为 dbo.GetSiteData 的存储过程.

  1. Execute the scripts given under SQL Scripts section in an SQL Server database to create a table named dbo.Source and populate with data (similar to data given in the question). It also creates a stored procedure named dbo.GetSiteData.

在 SSIS 包的连接上，创建一个 OLE DB 连接以连接到 SQL Server.我们将在后面的步骤中使用 Excel 连接.

On the SSIS package's connection, create an OLE DB connection to connect to the SQL Server. We will get to Excel connect later in the steps.

在 SSIS 包上，创建 6 个变量，如屏幕截图 #1 所示.使用值 Template 填充变量 Site，这是一个将用于评估其他表达式的示例值.使用值 SELECT DISTINCT Site FROM dbo.SourceData 设置变量 SQLUniqueSites.将变量 ExcelFolder 设置为值 C: emp

On the SSIS package, create 6 variables as shown in screenshot #1. Populate the variable Site with value Template, this is a sample value that will be used to evaluate other expressions. Set the variable SQLUniqueSites with value SELECT DISTINCT Site FROM dbo.SourceData. Set the variable ExcelFolder with value C: emp

选择变量ExcelFilePath并按F4查看属性.将属性 EvaluateAsExpression 更改为 True 并将属性 Expression 设置为值 @[User::ExcelFolder] + @[User::站点] + ".xls".请参阅屏幕截图 #2.

Select the variable ExcelFilePath and press F4 to view Properties. Change the property EvaluateAsExpression to True and set the property Expression with value @[User::ExcelFolder] + @[User::Site] + ".xls". Refer screenshot #2.

选择变量ExcelSheet并按F4查看属性.将属性 EvaluateAsExpression 更改为 True 并使用 ExcelSheet 变量值 下给定的值设置属性 Expression.请参阅屏幕截图 #3.

Select the variable ExcelSheet and press F4 to view Properties. Change the property EvaluateAsExpression to True and set the property Expression with value given under ExcelSheet Variable Value. Refer screenshot #3.

在 SSIS 包的控制流"选项卡上，放置一个执行 SQL 任务 并按屏幕截图 #4 和 #5强>.此任务将获取唯一的站点名称.

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:

相关文章