SSIS:调用执行模块化包集合设计与实现

     关于标题,详细说明下，目前维系DW的包集合中有按照模块划分存在相互依赖性的包，他们之间的调用需要有次序，比如staging_A的包发生错误，重新执行这个包需要考虑把依赖或者被依赖这个包的包找出来优先或执行完改包后执行。打个比方，有A,B,C三个包，UI展现基于C包完成，C依赖与B，B依赖与A，如果A包发生失败，只执行A包，UI的数据仍不是修复完后的数据，执行A需要级联执行依赖与A的子节点的包。

    如何设计按业务划分模块化执行依赖的包集合，必不可少的步骤是需要维护包之间的依赖关系，在这里简单的介绍两种维护方法，第一种，按业务整理把同一模块的包集合按照先后顺序通过SSIS父子包整理出多个模块的父包，哪个包发生失败，执行对应的模块父包，这种方法比较直观，操作和整理相对都比较容易，缺点也很明显，每个模块的包执行从最初的源头开始执行，对于从任意包开始执行，这种做法会导致跑了不必要跑的包，并且包之间的依赖关系比较复杂，会有同一个包在多个模块中，此时这种处理就不是那么妥善；第二种，在DB中用一张表去维护所有包之间的依赖关系，此时需要用到树形结构的父子节点关系，表的设计主要包含三种信息，当前包的ID，包的名称，包的父包节点ID。依靠这三种信息，通过递归算法，便可以轻松地获取任意包开始往下的所有被依赖的包即所选取的包下所有的子节点，当然，维护这层关系需要逻辑严密，思路清晰。

  简单的表结构如下：

这张表没有主键，由于包和包之间的依赖关系比较复杂，会存在多个父节点对应多个字节点。

CREATE TABLE [dbo].[PackageSequence](
	[ID] [int] NULL,
	[PackageName] [varchar](255) NULL,
	[ParentID] [int] NULL
) ON [PRIMARY]

对应的存储过程如下：

USE [LenovoDW]
GO
/****** Object:  StoredProcedure [dbo].[dw_sp_executePackage]    Script Date: 01/19/2015 12:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[dw_sp_executePackage] 
    -- Add the parameters for the stored procedure here
    @PathName nvarchar(256),
    @PackageName nvarchar(256)
AS
BEGIN

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#sequence') is not null
drop table #sequence
    
    /****************************************************************
    * 递归遍历所有传入包节点下的所有子节点包
    * 写入队列#sequence中
    ****************************************************************/
    ;with cte as
    (
    select ID,PackageName,ParentID from [SSISConfig].[dbo].[PackageSequence]
    where PackageName = @PackageName
    union all
    select a.ID,a.PackageName,a.ParentID from cte
    join [SSISConfig].[dbo].[PackageSequence] a 
    on cte.ID = a.ParentID  
    )
    
    select 
     IDENTITY(INT,1,1) DES_ID 
    ,PackageName
    --,'DTExec /FILE ^"' + @PathName +'\' + PackageName + '^" ' as sqlquery
    ,PackageName as sqlquery
    INTO #sequence 
    from cte
    
    /****************************************************************
    * 删除递归遍历中重复遍历的节点,对于重复的节点,保留最大的节点
    * 为什么呢,因为这样能保证正确的先后依赖顺序
    ****************************************************************/
    DElETE FROM #sequence
    WHERE DES_ID NOT IN (
    SELECT Max(DES_ID) FROM #sequence 
    group by PackageName 
    )
   
    alter table #sequence drop column DES_ID
    /****************************************************************
    *           添加自增列,用于循环逐行
    ****************************************************************/
    alter table #sequence add id int identity(1,1)
    
    /****************************************************************
    * 查询当前需要执行的包集合中是否有正在Run的包，有则直接return
    * 定义的逻辑：
    *             在两个小时内有执行且仍在执行状态的包在当前需要执
    *             行的队列#sequence中
    ****************************************************************/
    SELECT 
    p.PackageName
    FROM SSISConfig.dbo.PackageLog l
    JOIN SSISConfig.dbo.PackageVersion v ON l.PackageVersionID=v.PackageVersionID
    JOIN SSISConfig.dbo.Package p ON v.PackageID=p.PackageID
    WHERE Status ='R'
    AND StartDateTime >= dateadd(hh,-2,GETDATE())
    AND p.PackageName IN (
    SELECT PackageName FROM #sequence
    )
    
    IF @@ROWCOUNT>0
    BEGIN
     RETURN 
    END
    
    /****************************************************************
    *             循环执行队列中#sequence的包
    ****************************************************************/
    DECLARE @SQLQuery nvarchar(2000)
    DECLARE @ID INT 
    --SET @SQLQuery = 'DTExec /FILE ^"D:\test\test1\' + @PackageName + '^" '
    SET @SQLQuery = ''
    SET @ID = 1
    WHILE @ID<=(SELECT MAX(ID) FROM #sequence)
    BEGIN
        SELECT @SQLQuery = SQLQUERY FROM #sequence
        WHERE @ID = ID
        print @SQLQuery
        EXEC master..xp_cmdshell @SQLQuery
        SET @ID = @ID + 1
    END


END

处理的逻辑还不是很完善，目前只是整理的第一版，基本能够实现如下的需求。