如何使用SSIS包将表数据拆分为单独的命名Excel文件？ [英] How to split table data into separate named Excel files using an SSIS package?

问题描述

我正在使用SQL Server中的一组数据，我想进入一组Excel文件。这个任务需要自动化，每月运行一次。数据看起来像

 网站ID名字姓氏
 ------ -------  - -------- --------- 
北111吉姆·史密斯
北112蒂姆·约翰逊
北113 Sachin Tedulkar 
南201 Horatio阿尔及尔b $ b南205 Jimi Hendrix 
南215 Bugs兔子
  

我想要结果看起来像

 在名为North.xls的Excel文件中
 
 ID名字姓氏
 111 Jim Smith 
 112 Tim Johnson 
 113 Sachin Tedulkar 
 
在名为South.xls的Excel文件中
 
 ID名字姓氏
 201 Horatio Alger 
 205 Jimi Hendrix 
 215 Bugs兔子
  

有70到100个值我要拆分的站点列。我使用SSIS来执行这个任务，但是在使用OLE DB Source任务从SQL Server中提取数据后，我被困住了。接下来应该怎么样如果有更简单的方法可以使用其他工具，我也可以这样做。

解决方案

你可能已经找到了现在回答你的问题。这是对于可能绊倒这个问题的其他用户。以下示例显示了如何为可能存在的任意数量的网站动态实现。该示例使用 SSIS 2008 R2 与 SQL Server 2008 R2 数据库创建。

分步过程

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




2. 在SSIS包的连接上，创建一个OLE DB连接以连接到SQL Server。我们将在以后的步骤中获得Excel连接。




3. 在SSIS包中，创建6个变量，如截图＃ 1 所示。使用值 Template 填充变量站点，这是一个用于评估其他表达式的示例值。设置变量 SQLUniqueSites ，值为 SELECT DISTINCT Site FROM dbo.SourceData 。设置变量 ExcelFolder ，值为 C：\temp\




4. 选择变量 ExcelFilePath ，然后按 F4 查看属性。将属性 EvaluateAsExpression 更改为 True ，并将值 @ [User： ：ExcelFolder] + @ [User :: Site] +.xls。请参阅截图＃ 2 。




5. 选择变量 ExcelSheet ，然后按 F4 查看属性。将属性 EvaluateAsExpression 更改为 True ，并使用 ExcelSheet变量值下的值设置属性 Expression STRONG>。请参阅截图＃ 3 。




6. 在SSIS包的控制流选项卡上，放置一个执行SQL任务并配置它，如屏幕截图＃ 4 和＃ 5 所示。此任务将获取唯一的站点名称。




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




8. 在Foreach循环容器中，放置一个执行SQL任务，然后放置一个数据流任务。此时，控制流选项卡应该如截图＃ 8 所示。此时您的软件包可能会显示错误，我们将在以后的步骤中进行修复。我们将配置数据流任务，然后返回执行Foreach循环容器中的SQL任务。




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




10. 如果表字段数据类型在VARCHAR中，那么我们需要将其转换为NVARCHAR（unicode格式），否则不需要此步骤。在数据流任务中，在OLE DB Source之后放置数据转换转换，并将其配置如截图＃ 12 所示。




11. 接下来，在数据流任务中，放置一个Excel目标，点击第一个新建...按钮，如截图＃ 13 所示。




12. 在Excel连接管理器对话框中，提供Excel文件路径，然后单击确定。请参阅截图＃ 14 。返回Excel目标，点击第二个新建...按钮，如截图＃ 15 所示。在创建表对话框中，确保脚本如截图＃ 16 所示，然后单击确定。当显示为截图＃ 17 时显示警告，单击确定。从 Excel表单名称下拉列表中选择 Template 的值，如截图＃ 18 所示。配置列，如截图＃ 19 所示。




13. 在SSIS软件包的连接管理器上，选择新创建的Excel连接管理器按F4查看属性。将名称属性值更改为 Excel 。将 DelayValidation 更改为 True ，以便如果Template.xls文件不存在，则不会收到错误消息。设置值为 @ [USer :: ExcelFilePath] 的 ServerName 表达式。参考截图＃ 20 。 注意： 应该在路径C：\temp\Template.xls中创建一个Excel文件。您可能希望保存它，以便在将来的设计更改期间不会遇到。如果文件被删除，您仍然可以重新创建它。




14. 返回到Excel目标并进行配置，如截图＃ 21 。一旦数据流任务被配置，它应该看起来像截图＃ 22 所示。




15. 回到控制流选项卡，在Foreach Loop容器中配置执行SQL任务，如截图＃ 23 所示。此任务将为每个站点名称创建新的Excel电子表格。




16. 截图＃ 24 显示文件夹中的内容c：\temp\\




17. 截图＃ 25 和＃ 26 显示包执行。




18. 截图＃ 27 显示文件夹中的内容c：\temp\ after 包执行。




19. 截图＃ 28 和＃ 29 显示新创建的Excel电子表格North.xls和South.xls。两张表都包含相同名称的网站。

希望有帮助。 b
$ b

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 （网站，名字，姓氏）VALUES 
（'North'，'Jim'，'Smith'），
（'North'，'Tim'，'Johnson'），
 ''''''''''''''''''''''''' drib'），
（'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： p>

截图＃9：

截图＃10：

img src =https://i.stack.imgur.com/YA6KJ.pngalt =10>

截图＃11： strong>

截图＃12：

截图＃13：

截图＃ 14：

截图＃15：

截图＃16： / p>

截图＃17：

截图＃18：

截图＃19： / strong>

截图＃20：

截图＃21：

屏幕截图＃22：

截图＃23：

< img src =https://i.stack.imgur.com/luL0R.pngalt =23>

截图＃24： strong>

截图＃25：

截图26：

截图＃ 27：

截图＃28：

截图＃29：

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

I'd like the results to look like

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

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.

解决方案

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.

Step-by-step process:

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.

2. 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.

3. 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:\temp\

4. 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.

5. 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.

6. 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.

7. 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.

8. 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.

9. 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.

10. 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.

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

12. 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.

13. 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:\temp\Template.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.

14. 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.

15. 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.

16. Screenshot #24 shows contents in folder c:\temp\ before the package execution.

17. Screenshots #25 and #26 show the package execution.

18. Screenshot #27 shows contents in folder c:\temp\ after the package execution.

19. 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.

Hope that helps.

ExcelSheet Variable Value:

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

SQL Scripts:

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

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

Screenshot #10:

Screenshot #11:

Screenshot #12:

Screenshot #13:

Screenshot #14:

Screenshot #15:

Screenshot #16:

Screenshot #17:

Screenshot #18:

Screenshot #19:

Screenshot #20:

Screenshot #21:

Screenshot #22:

Screenshot #23:

Screenshot #24:

Screenshot #25:

Screenshot #26:

Screenshot #27:

Screenshot #28:

Screenshot #29:

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