USP_DATALIST_OLAPDATASOURCE_ETLHISTORY_CHART

Used for the OLAP data source ETL History chart dashboard.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDECHILDPACKAGES	bit	IN	Include child packages
@SSISPACKAGENAME	nvarchar(255)	IN	SSIS package name
@INCLUDEFULLREFRESH	bit	IN	Include full refresh
@INCLUDEINCREMENTALREFRESH	bit	IN	Include incremental refresh
@MAXETLROWS	int	IN	Max rows

Definition

Copy



CREATE procedure dbo.USP_DATALIST_OLAPDATASOURCE_ETLHISTORY_CHART
@CONTEXTID uniqueidentifier,
@INCLUDECHILDPACKAGES bit=0,
@SSISPACKAGENAME nvarchar(255)= null,
@INCLUDEFULLREFRESH bit=1,
@INCLUDEINCREMENTALREFRESH bit=1,
@MAXETLROWS int=300

as

set nocount on;

declare @PACKAGEFILTER nvarchar(256);
set @PACKAGEFILTER=COALESCE(@SSISPACKAGENAME,'') + '%';

if COALESCE(@MAXETLROWS,0)=0 
  set @MAXETLROWS=300;

select TOP (@MAXETLROWS)
ID,
ETLSTARTTIME,
ETLENDTIME,
DATEDIFF(MINUTE,ETLSTARTTIME,ETLENDTIME) AS TOTALMINUTES,
ETLCONTROLID,
SSISPACKAGENAME,
ISPARENT AS ISPARENTPACKAGE,

CASE WHEN DATAWINDOWOPEN <= '1776-07-04' THEN 'Full' ELSE 'Incremental' END AS REFRESHTYPE,

CAST(CASE WHEN DATAWINDOWOPEN >  '1776-07-04' THEN 1 ELSE 0 END AS BIT) AS ISINCREMENTALREFRESH

from dbo.ETLHISTORY 


where 
  OLAPDATASOURCEID = @CONTEXTID
and
  COMPLETED=1
and
(@INCLUDECHILDPACKAGES=1 OR ISPARENT<>0)

AND
(
  (@SSISPACKAGENAME IS NULL)
  OR
  (@SSISPACKAGENAME='')
  OR
  (SSISPACKAGENAME LIKE @PACKAGEFILTER)

 )

 AND
 (
  @INCLUDEINCREMENTALREFRESH=1
 OR 
 DATAWINDOWOPEN <= '1776-07-04'
 )

 AND
 (
  @INCLUDEFULLREFRESH=1
 OR 
 DATAWINDOWOPEN > '1776-07-04'
 )


 ORDER BY ETLSTARTTIME DESC
 ;