sql-server - SSIS 转换：SQL 子查询到 SSIS 任务

Question

我正在尝试使用 SSIS 深入研究 ETL 的“转换”部分。我需要使用 SSIS 任务通过 SQL 查询（存储过程）完成任务。

输出是产生具有百分比贡献的前 5 个三元组。我已经模拟了我在 SQL 存储过程中所做的事情。

你能指导我怎么做吗？

--Preparing Dataset
CREATE TABLE temp20190716
    (
        WeekNumber INT,
        Triplet VARCHAR(50),
        Product VARCHAR(50) ,
        CustomerType VARCHAR(50),
        Occurence INT
    )

INSERT INTO temp20190716
    (
    WeekNumber ,
    Triplet ,
    Product ,
    CustomerType ,
    Occurence 
    )
VALUES 
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4) ,'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4) ,'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4) ,'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4) ,'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Mobile','Consumer',FLOOR(RAND()*(25-10+1))+10),

    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),       
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),       
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10) ,
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),       
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10) ,
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),       
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10),
    (201915,SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4)+':'+SUBSTRING(CONVERT(varchar(255), NEWID()),0,4),'Fixed','Consumer',FLOOR(RAND()*(25-10+1))+10) 

需要使用 SSIS 达到以下输出

SELECT 
*
FROM
(SELECT TOP 5
    Triplet,
    SUM(Occurence)Sort,
    Triplet+', '+CONVERT(VARCHAR,SUM(Occurence))+', '+ CONVERT(VARCHAR,SUM(Occurence)*100/(SELECT SUM(Occurence) FROM temp20190716 WHERE Product IN ('Mobile') AND CustomerType='Consumer' ))+' %' DisplayName,
    'Consumer Mobile' Tag ,
    CustomerType
FROM temp20190716
WHERE Product IN ('Mobile') AND CustomerType='Consumer'
GROUP BY Triplet ,CustomerType
ORDER BY 2 DESC
UNION 
SELECT TOP 5
    Triplet,
    SUM(Occurence)Sort,
    Triplet+', '+CONVERT(VARCHAR,SUM(Occurence))+', '+ CONVERT(VARCHAR,SUM(Occurence)*100/(SELECT SUM(Occurence) FROM temp20190716 WHERE Product IN ('Fixed') AND CustomerType='Consumer' ))+' %' DisplayName,
    'Consumer Fixed' Tag ,
    CustomerType
FROM temp20190716
WHERE Product IN ('Fixed') AND CustomerType='Consumer'
GROUP BY Triplet ,CustomerType
ORDER BY 2 DESC
)X

SQL 中的输出：

标签： sql-serverssisetl

Answer 1

如果您的经理绝对不允许您使用将该查询作为存储过程运行的最佳实践，您可以通过两种方式解决此限制，具体取决于您希望如何使用数据。

选项 #1：将您的查询用作源连接。您可以创建一个使用查询而不是表/视图的OLE DB Sourcein 。Data Flow Task它看起来像这样：

选项 #2：Execute SQL Task在Control Flow. 在您的Execute SQL Task设置中，您将ResultSet属性设置为Full Result Set并将其映射到 Object 类型的变量。然后，您可以像使用任何其他数据集对象一样使用该对象。如果您需要迭代地处理每一行，这将是首选解决方案，例如在Foreach Loop.