USP_INITIALIZEQUEUE

CREATE procedure BBDW.[USP_INITIALIZEQUEUE] as

--Add any new/missing packages to the package list
insert into BBDW.[PACKAGELIST]([PACKAGE], [LASTRUNTIME])
  select
    distinct [PACKAGE], 0
  from BBDW.[PACKAGEDEPENDENCIES]
  where [PACKAGE] not in
  (select [PACKAGE] from BBDW.[PACKAGELIST]);

--Delete disabled packages
  delete
  from BBDW.[PACKAGELIST]
  where [PACKAGE] not in (select [PACKAGE] from BBDW.[PACKAGEDEPENDENCIES])
  or [PACKAGE] in (select [PACKAGE] from BBDW.[PACKAGEDEPENDENCIES] where [ENABLED] = 0);

--Add DeletedIDs and DimDate as dependencies for everyone else
insert into BBDW.[PACKAGEDEPENDENCIES] ([PACKAGE], [DEPENDENCY], [ENABLED])
  select
      [PACKAGE],
      'BBDW_DELETEDIDS.dtsx' as [DEPENDENCY],
      1 as [ENABLED]
    from BBDW.[PACKAGELIST]
    where [PACKAGE] not in ('BBDW_DELETEDIDS.dtsx', 'BBDW_DIM_DATE.dtsx')

  union all

  select
      [PACKAGE],
      'BBDW_DIM_DATE.dtsx' as [DEPENDENCY],
      1 as [ENABLED]
    from BBDW.[PACKAGELIST]
    where [PACKAGE] not in ('BBDW_DELETEDIDS.dtsx', 'BBDW_DIM_DATE.dtsx');

--Remove empty/placeholder dependencies    
delete from BBDW.[PACKAGEDEPENDENCIES] where [DEPENDENCY] = '' and [PACKAGE] not in ('BBDW_DELETEDIDS.dtsx', 'BBDW_DIM_DATE.dtsx');

--Make sure all packages are not marked as completed
  update BBDW.[PACKAGELIST]
  set [ISCOMPLETE] = 0;

--Add Deleted IDs and DimDate to the queue first
  insert into BBDW.[PACKAGEQUEUE]([PACKAGE], [LASTRUNTIME])
    values('BBDW_DELETEDIDS.dtsx', 0), ('BBDW_DIM_DATE.dtsx', 0);

  declare @TOTALPACKAGECOUNT int;
  declare @PACKAGEQUEUECOUNT int;
  declare @PACKAGEPROCESSEDCOUNT int;

  set @TOTALPACKAGECOUNT = (select count(1) from BBDW.[PACKAGELIST]);
  set @PACKAGEQUEUECOUNT = (select count(1) from BBDW.[PACKAGEQUEUE]);
  set @PACKAGEPROCESSEDCOUNT = (select count(1) from BBDW.[PACKAGELIST] where [ISCOMPLETE] = 1);

--Return package counts
  select
    @TOTALPACKAGECOUNT as [TOTALPACKAGECOUNT],
    @PACKAGEQUEUECOUNT as [PACKAGEQUEUECOUNT],
    @PACKAGEPROCESSEDCOUNT as [PACKAGEPROCESSEDCOUNT];