USP_ETL_BEGINLOAD

CREATE procedure [BBDW].[USP_ETL_BEGINLOAD]
@CURRENTDATAWINDOWCLOSE datetimeoffset=NULL, --should be relative to the source system, use SELECT SYSDATETIMEOFFSET() from the source system.
                                             --for child packages will use the DATAWINDOWCLOSE of the parent.
@ISPARENT bit,
@SSISPACKAGENAME nvarchar(255),
@SSISPACKAGEPATH nvarchar(255) ='',
@SSISSTARTTIME datetimeoffset =null,
@SSISUSERNAME nvarchar(255) ='',
@SSISMACHINENAME nvarchar(255) ='',
@SSISEXECUTIONINSTANCEGUID uniqueidentifier =null,
@CLEAR_INCOMPLETE_LOADS bit=1 --true to delete where LOADISCOMPLETE=0;
as
  set nocount on;

  declare @CURRENTDATAWINDOWOPEN datetimeoffset;
  declare @ETLCONTROLID int;
  declare @VALIDSTARTDATEDIMID int;
  declare @VALIDENDDATEDIMID int;

  begin try

    declare @PARENTETLCONTROLID int;

    if @ISPARENT =0
    begin
      select top 1 @PARENTETLCONTROLID = [ETLCONTROLID]
      from BBDW.[ETLCONTROL] where [LOADISCOMPLETE] = 0 and [ISPARENT]=1 order by [DATAWINDOWCLOSE] desc, [ETLCONTROLID] desc;

      if @PARENTETLCONTROLID is null
      begin
        raiserror('Unable to find an executing parent ETLCONTROLID', 16, 1);
        return 68;
      end
    end


    if (@CURRENTDATAWINDOWCLOSE IS NULL) AND (@ISPARENT=1)
      begin
        raiserror ('@CURRENTDATAWINDOWCLOSE cannot be NULL.  Select GETDATE() from the source system and use that value for @CURRENTDATAWINDOWCLOSE', 16, 1);
        return 70;
      end

    if (@CURRENTDATAWINDOWCLOSE IS NULL) AND (@ISPARENT = 0)
      begin
        select @CURRENTDATAWINDOWCLOSE = max([DATAWINDOWCLOSE]) 
        from [ETLCONTROL] 
        where [ETLCONTROLID] = @PARENTETLCONTROLID
      end

    begin transaction;

    if @CLEAR_INCOMPLETE_LOADS = 1 and (@ISPARENT=1)
      delete from BBDW.[ETLCONTROL]
        where [LOADISCOMPLETE] = 0;

    select @CURRENTDATAWINDOWOPEN = max([DATAWINDOWCLOSE])
    from BBDW.[ETLCONTROL]
    where [LOADISCOMPLETE] = 1 and [SSISPACKAGENAME] = @SSISPACKAGENAME;

    select
      @VALIDSTARTDATEDIMID = min([DATEDIMID]),
      @VALIDENDDATEDIMID = max([DATEDIMID])
    from BBDW.[DIM_DATE]
    where [DATEDIMID] > 0;

    if @CURRENTDATAWINDOWOPEN is null
      set @CURRENTDATAWINDOWOPEN='1776-07-04 00:00:00.0000000 +00:00'


    -- Set load parameters - use the last load and the current datetime to determine load window

    insert into BBDW.[ETLCONTROL](
      [DATAWINDOWOPEN],
      [DATAWINDOWCLOSE],
      [LOADISCOMPLETE],
      [ETLSTARTTIME],
      [SSISPACKAGENAME],
      [SSISPACKAGEPATH],
      [SSISSTARTTIME],
      [SSISUSERNAME],
      [SSISMACHINENAME],
      [PARENTETLCONTROLID],
      [ISPARENT],
      [SSISEXECUTIONINSTANCEGUID],
      [NUMROWSADDED],
      [NUMROWSUPDATED]
      )
    values(
      @CURRENTDATAWINDOWOPEN,
      @CURRENTDATAWINDOWCLOSE,
      0,
      SYSDATETIMEOFFSET(),
      @SSISPACKAGENAME ,
      @SSISPACKAGEPATH,
      @SSISSTARTTIME ,
      @SSISUSERNAME ,
      @SSISMACHINENAME ,
      @PARENTETLCONTROLID,
      @ISPARENT,
      @SSISEXECUTIONINSTANCEGUID,
      0,
      0
      );


    set @ETLCONTROLID = @@IDENTITY

    commit transaction;

    --RETURN a result set to avoid the SSIS parameter hassle.
    select @ETLCONTROLID as [ETLCONTROLID],
    cast(@CURRENTDATAWINDOWOPEN as datetime) as [DATAWINDOWOPEN],
    cast(@CURRENTDATAWINDOWCLOSE as datetime) as [DATAWINDOWCLOSE],
    @CURRENTDATAWINDOWOPEN as [DATAWINDOWOPEN_DATETIMEOFFSET],
    @CURRENTDATAWINDOWCLOSE as [DATAWINDOWCLOSE_DATETIMEOFFSET],
    @VALIDSTARTDATEDIMID as [VALIDSTARTDATEDIMID],
    @VALIDENDDATEDIMID as [VALIDENDDATEDIMID];

  end try
  begin catch
    if @@TRANCOUNT > 0 rollback transaction;
    exec BBDW.USP_RAISE_ERROR;
    raiserror('There was an error in procedure USP_ETL_BEGINLOAD while setting the load parameters.', 16, 1);
    return 50;
  end catch