从 SSIS 中数据流任务中的逗号分隔字段填充数据透视表

Question

这是我的第一篇文章，所以要温柔:)。我正在构建一个 SSIS 包来收集整个企业中运行的作业的信息。目标是让表格填充有关服务器、实例、数据库、作业及其相互关系的信息。理想情况下，我将使用合并命令在必要时更新或插入，但现在它只是插入。以下是我的软件包的概述（请不要胡思乱想：P）：（

对新用户的超链接的限制意味着您需要将我的链接中的 + 符号替换为“t”

http://img262.imageshack.us/i/package.jpg/

第一个数据流任务拉入列表从平面文本文件中检查实例并将它们存储在记录集中，然后 foreach 循环遍历每个实例并相应地更改我的源连接管理器的连接字符串。 “进程服务器”数据流任务用于查找服务器名称并将其添加到目标数据库（如果不存在），无论哪种方式，它也会将服务器 ID 和名称存储在包变量中。“进程实例”执行相同的操作。与上面相同，但对于实例，“收集数据库数据”任务然后使用这些包变量插入该实例中的所有数据库，作为带有上面提到的外键包变量的记录。完成后，我们将继续执行“收集作业数据”任务（希望是此包的最终任务）。以下是最后一个任务的内容：

http://img809.imageshack.us/i/ dataflow.png/

因此，在这个任务中，这就是我到目前为止正在做的事情。我首先使用查询来收集工作信息和维护计划数据。这是 OLE DB 源的查询：

--WRITTEN BY MAXWELL WALLACE
--THE PURPOSE OF THIS QUERY IS TO COLLECT INFORMATION ABOUT JOBS AND MAINTENANCE PLANS
--RUNNING ON A PARTICULAR INSTANCE. IT COLLECTS NAMES, STATUSES, RUN TIMES AND DATES
--AS WELL AS DATABASES AFFECTED AND MAINTENANCE PLAN NAMES IF APPLICABLE.
SELECT B.NAME AS JOB_NAME, B.CATEGORY_ID, 
--RUN_STATUS CODE GETS TRANSLATED INTO ENGLISH
CASE A.RUN_STATUS
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
ELSE 'Unknown'
END AS RUN_STATUS,
--CONVERT INTEGER DATE INTO SOMETHING MORE LEGABLE
SUBSTRING(CAST(A.RUN_DATE AS CHAR(8)),5,2) + '/' + 
RIGHT(CAST(A.RUN_DATE AS CHAR(8)),2) + '/' + 
LEFT(CAST(A.RUN_DATE AS CHAR(8)),4) AS RUN_DATE,
--CONVERT RUN_TIME INTO SOMETHING MORE RECONGNIZABLE (HH:MM:SS)
LEFT(RIGHT('000000' + CAST(A.RUN_TIME AS VARCHAR(10)),6),2) + ':' + 
SUBSTRING(RIGHT('000000' + CAST(A.RUN_TIME AS VARCHAR(10)),6),3,2) + ':' + 
RIGHT(RIGHT('000000' + CAST(A.RUN_TIME AS VARCHAR(10)),6),2) AS RUN_TIME,
--CONVERT RUN_DURATION INTO SOMETHING MORE RECONGNIZABLE (HH:MM:SS)
LEFT(RIGHT('000000' + CAST(A.RUN_DURATION AS VARCHAR(10)),6),2) + ':' + 
SUBSTRING(RIGHT('000000' + CAST(A.RUN_DURATION AS VARCHAR(10)),6),3,2) + ':' + 
RIGHT(RIGHT('000000' + CAST(A.RUN_DURATION AS VARCHAR(10)),6),2) AS RUN_DURATION,
--THE FOLLOWING SUBQUERY IS USED TO EXTRAPOLATE DETAILS FOR THE JOB IN IT'S MAINTENANCE PLAN (IF IT HAS 1)
--THE TOP 1 MAKES SURE WE GET ONLY 1 RECORD SINCE THIS IS A 1 TO MANY RELATIONSHIP
--THE LINE3 COLUMN CONTAINS DETAILS ABOUT THE TASK THAT WAS RUN
(SELECT TOP 1 E.LINE3 
    --WE START WITH THE SYSMAINTPLAN_LOG BECAUSE WE CAN (IN A WAY) JOIN IT TO OUR OUTER JOIN THROUGH THE PLAN_ID IN THE WHERE CLAUSE
    FROM MSDB.DBO.SYSMAINTPLAN_LOG AS D 
    --NOW IT IS POSSIBLE TO, BY EXTENTION, JOIN SYSMAINTPLAN_LOGDETAIL TO THE OUTER JOIN AS WELL THROUGH ITS 1 TO 1 RELATIONSHIP WITH SYSMAINTPLAN_LOG
    INNER JOIN MSDB.DBO.SYSMAINTPLAN_LOGDETAIL AS E ON E.TASK_DETAIL_ID = D.TASK_DETAIL_ID
    --THE 1ST PART OF THE WHERE RETURNS ONLY RECORDS OF THE SAME PLAN_ID, ESSENTIALLY "JOINING" THIS RECORD TO THE OUTER JOIN THE IN MAIN QUERY
    --THE 2ND PART MAKES SURE THE FIELD WE ACTUALLY CARE ABOUT CONTAINS MEANINGFUL DATA
    WHERE D.PLAN_ID = C.PLAN_ID AND E.LINE3 != '') AS PLAN_DETAILS, 
--THE FOLLOWING SUBQUERY RETURNS THE NAME OF THE MAINTENANCE PLAN (IF IT HAS 1)
(SELECT F.NAME
    FROM MSDB.DBO.SYSMAINTPLAN_PLANS AS F --THIS IS A SYSTEM GENERATED VIEW
    --LIKE THE ABOVE SUBQUERY, THIS WHERE ESSENTIALLY "JOINS" THIS RECORD TO THE OUTER JOIN IN THE MAIN QUERY
    WHERE F.ID = C.PLAN_ID) AS PLAN_NAME 
FROM MSDB.DBO.SYSJOBHISTORY AS A
INNER JOIN MSDB.DBO.SYSJOBS AS B ON A.JOB_ID = B.JOB_ID
--THIS OUTTER JOIN ATTACHES PLAN_IDS OF MAINTENANCE PLANS TO JOBS THAT HAVE THEM
LEFT OUTER JOIN SYSMAINTPLAN_SUBPLANS AS C ON C.JOB_ID = B.JOB_ID
--ONLY RETURN ENABLED JOBS
WHERE B.[ENABLED] = 1 
--AND ONLY JOB OUTCOMES, NOT EACH STEP
AND A.STEP_ID = 0 
--AND ONLY COMPLETED JOBS
AND A.RUN_STATUS <> 4
--SORTED BY LATEST DATE 1ST
ORDER BY A.RUN_DATE DESC

抱歉，但由于某种原因，该论坛不保留我的格式。无论如何，之后我将包变量实例 ID 添加为一列，以帮助使用此外键插入这些记录。我将一些字符串转换为不存在的 unicode，然后对有维护计划的记录和没有维护计划的记录进行条件分割。对于没有我可以的记录，只需将它们插入目的地即可完成！然而，对于具有维护计划的记录，它们连接到一个或多个数据库的机会非常高。因此，首先我将作业记录插入到作业表中（与处理不在维护计划内的记录完全相同），然后进行查找以查找刚刚插入的记录的 ID。接下来，我从查询中获取字段，该字段具有逗号分隔的数据库列表，该列表受此作业所属的维护计划的影响，并将其分隔到 VB.Net ArrayList 中。然后我将该 ArrayList 分配给包变量。

这就是我现在所处的部分。显然，我的下一步是使用我刚刚查找的作业 ID 创建某种循环，并运行 ArrayList 中的每个变量，将它们一次插入 1 个到 DB/作业数据透视表中。问题是我不知道如何在数据流任务中进行循环，并且我想不出将数据透视表插入移出该任务的好方法。我也许可以使用脚本组件来完成此操作，但我不确定如何从脚本任务中执行插入（我是否应该考虑这一点？）。我精通 VB.Net、C# 以及 TSQL，因此我可以研究实现此目的的任何方法。预先感谢您的帮助。干杯!

附言。这是我将数据插入其中的表结构：

CREATE TABLE TBL_SERVERS(
ID INT UNIQUE IDENTITY(1,1),
TITLE NVARCHAR(50) PRIMARY KEY,
CLUSTER_NAME NVARCHAR(50) DEFAULT '',
RESOURCES_USED NVARCHAR(20) DEFAULT '',
RESOURCE_THRESHOLD NVARCHAR(20) DEFAULT '',
IS_CLUSTERED BIT NOT NULL DEFAULT 0)

CREATE TABLE TBL_INSTANCES(
ID INT UNIQUE IDENTITY(1,1),
SERVER_ID INT NOT NULL REFERENCES TBL_SERVERS(ID),
TITLE NVARCHAR(50) NOT NULL,
PRIMARY KEY (SERVER_ID,TITLE))

CREATE TABLE TBL_CATEGORY_TYPES(
ID INT UNIQUE IDENTITY(1,1),
TITLE NVARCHAR(50) PRIMARY KEY)

INSERT INTO TBL_CATEGORY_TYPES VALUES ('LOCAL')
INSERT INTO TBL_CATEGORY_TYPES VALUES ('MULTISERVER')
INSERT INTO TBL_CATEGORY_TYPES VALUES ('NONE')

CREATE TABLE TBL_CATEGORY_CLASSES(
ID INT UNIQUE IDENTITY(1,1),
TITLE NVARCHAR(50) PRIMARY KEY)

INSERT INTO TBL_CATEGORY_CLASSES VALUES ('JOB')
INSERT INTO TBL_CATEGORY_CLASSES VALUES ('ALERT')
INSERT INTO TBL_CATEGORY_CLASSES VALUES ('OPERATOR')

CREATE TABLE TBL_CATEGORIES(
ID INT UNIQUE IDENTITY(1,1),
TITLE NVARCHAR(50) NOT NULL,
CATEGORY_CLASS_ID INT NOT NULL REFERENCES TBL_CATEGORY_CLASSES(ID),
CATEGORY_TYPE_ID INT NOT NULL REFERENCES TBL_CATEGORY_TYPES(ID),
PRIMARY KEY (TITLE,CATEGORY_CLASS_ID))

CREATE TABLE TBL_SQL_JOBS(
ID INT PRIMARY KEY IDENTITY(1,1),
TITLE NVARCHAR(200) NOT NULL,
INSTANCE_ID INT NOT NULL REFERENCES TBL_INSTANCES(ID),
CATEGORY_ID INT NOT NULL REFERENCES TBL_CATEGORIES(ID),
RUN_STATUS NVARCHAR(10) NOT NULL,
RUN_DATE NVARCHAR(10) NOT NULL,
RUN_TIME NVARCHAR(8) NOT NULL,
RUN_DURATION NVARCHAR(8) NOT NULL,
MAINTENANCE_PLAN_NAME NVARCHAR(200),
RUN_INTERVAL NVARCHAR(20) DEFAULT '',
IS_ENABLED BIT NOT NULL DEFAULT 1)

SET IDENTITY_INSERT TBL_CATEGORIES ON
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (0,'[Uncategorized (Local)]',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (2,'[Uncategorized (Multi-Server)]',1,2,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (98,'[Uncategorized]',2,3,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (99,'[Uncategorized]',3,3,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (8,'Data Collector',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (7,'Database Engine Tuning Advisor',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (3,'Database Maintenance',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (5,'Full-Text',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (1,'Jobs from MSX',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (6,'Log Shipping',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (18,'REPL-Alert Response',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (16,'REPL-Checkup',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (10,'REPL-Distribution',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (11,'REPL-Distribution Cleanup',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (12,'REPL-History Cleanup',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (20,'Replication',2,3,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (13,'REPL-LogReader',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (14,'REPL-Merge',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (19,'REPL-QueueReader',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (15,'REPL-Snapshot',1,1,1)
INSERT INTO TBL_CATEGORIES (ID,TITLE,CATEGORY_CLASS_ID,CATEGORY_TYPE_ID,CATEGORY_GROUP_ID) VALUES (17,'REPL-Subscription Cleanup',1,1,1)
SET IDENTITY_INSERT TBL_CATEGORIES OFF

CREATE TABLE TBL_APPLICATIONS(
ID INT UNIQUE IDENTITY(1,1),
TITLE NVARCHAR(200) NOT NULL,
HUB_SITE NVARCHAR(50) DEFAULT '',
PRIMARY KEY (TITLE,HUB_SITE))

CREATE TABLE TBL_DATABASES(
ID INT UNIQUE IDENTITY(1,1),
INSTANCE_ID INT NOT NULL REFERENCES TBL_INSTANCES(ID),
TITLE NVARCHAR(200) NOT NULL,
APPLICATION_ID INT REFERENCES TBL_APPLICATIONS(ID),
MANAGED BIT NOT NULL DEFAULT 0,
CONNECTIONSTRING NVARCHAR(MAX) NOT NULL DEFAULT '',
RESOURCES_USED NVARCHAR(MAX) NOT NULL DEFAULT '',
RESOURCE_THRESHOLD NVARCHAR(MAX) NOT NULL DEFAULT '',
LAST_SEEN DATETIME NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (INSTANCE_ID,TITLE))

CREATE TABLE TBL_DATABASE_JOBS(
ID INT UNIQUE IDENTITY(1,1),
DATABASE_ID INT NOT NULL REFERENCES TBL_DATABASES(ID),
JOB_ID INT NOT NULL REFERENCES TBL_SQL_JOBS(ID),
PRIMARY KEY (DATABASE_ID,JOB_ID))

这是我之前发布的查询的一些示例结果。请记住，只要您使用 MSDB，该脚本就可以针对任何实例运行，因为它使用所有系统生成的表和视图：

http://img253.imageshack.us/i/resultsn.jpg/

请明确我对这个包的目标。 JOB_NAME、CATEGORY_ID、RUN_DATE、RUN_TIME、RUN_DURATION 和 PLAN_NAME 全部进入 TBL_SQL_JOBS 表。 PLAN_DETAILS 列不会对空值执行任何操作（PLAN_NAME 也会如此），但对于填充的记录，它将删除“Databases:”字符串并拆分逗号分隔的数据库名称。然后，它需要根据 TBL_Databases 表（之前填充的）检查拆分中的数据库名称并获取相应的 ID。然后，结合我们正在处理的当前作业记录的 ID（认为包的最后一个任务的“查找作业 ID”部分），我们将这些记录分别添加到 TBL_DATABASE_JOBS 表中。最终结果是一个包含唯一数据库列表的表和一个包含历史作业信息列表的表，以及一个提供 1 个作业：多个数据库关系的表。再次感谢。

Answer 1

听起来您的脚本解决方案是正确的选择。而且，您无需担心从脚本中插入。

您可以将脚本的角色限制为仅处理逗号分隔列表的拆分。对于列表中的每一项，脚本的输出将为一行。

重要的一步是将脚本组件设置为异步。异步模式使组件能够输出与输入行数不同的行数。创建新的脚本组件作为转换后，编辑该组件。选择输入和输出属性，选择输出 0。将 SynchronousInputID 属性的值更改为 None。展开输出 0 分支并将列添加到输出列分支。这些列将包含退出该组件的行。最后，添加脚本代码，该代码将为逗号分隔列表中的每个项目创建一行。

一旦您可以为列表中的每一项导出一行，就可以像添加任何其他目标一样添加目标。