Object Extension Points

The Object category has the following available Extension Points defined.

Append Source SQL

Append to the source query SQL generated by BimlFlex

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="AppendSourceSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<# 	CustomOutput.ObjectInherit = false; #>
FOR READ ONLY WITH CS

Azure Post Archive Stage

Configure pipeline logic that will be injected after the blobs are archived

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="AzurePostArchiveStage" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	var sourceObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage, "SRC");
	var targetObject = new TableObject(table, sourceObject.TargetConnection.IntegrationStage, "TGT");
	var persistentConnection = sourceObject.IsPersistent ? sourceObject.PersistentConnection : sourceObject.ProjectStageConnection;
	persistentConnection = new[] {"AZB", "AZDLS"}.Contains(persistentConnection.ConnectionType) ? sourceObject.ProjectTargetConnection : persistentConnection;
#>
<#	CustomOutput.ObjectInherit = true; #>
<ExecuteSQL Name="SQL - AzurePreArchiveStage" ConnectionName="<#=persistentConnection.Name#>">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Parameters>
		<Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
	</Parameters>
	<DirectInput>EXEC <#=targetObject.SystemType.StoredProcedureName(sourceObject, targetObject.Schema, targetObject.StartDelimiter, targetObject.EndDelimiter)#> ?</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - AzurePreArchiveStage.Output"; #>
</ExecuteSQL>

Azure Pre Archive Stage

Configure pipeline logic that will be injected before the blobs are archived

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="AzurePreArchiveStage" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	var sourceObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage, "SRC");
	var targetObject = new TableObject(table, sourceObject.TargetConnection.IntegrationStage, "TGT");
	var persistentConnection = sourceObject.IsPersistent ? sourceObject.PersistentConnection : sourceObject.ProjectStageConnection;
	persistentConnection = new[] {"AZB", "AZDLS"}.Contains(persistentConnection.ConnectionType) ? sourceObject.ProjectTargetConnection : persistentConnection;
#>
<#	CustomOutput.ObjectInherit = true; #>
<ExecuteSQL Name="SQL - AzurePreArchiveStage" ConnectionName="<#=persistentConnection.Name#>">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Parameters>
		<Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
	</Parameters>
	<DirectInput>EXEC <#=targetObject.SystemType.StoredProcedureName(sourceObject, targetObject.Schema, targetObject.StartDelimiter, targetObject.EndDelimiter)#> ?</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - AzurePreArchiveStage.Output"; #>
</ExecuteSQL>

Create SQL

Configure override Create DDL for an Object.

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the Object to which the SQL will be added.

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="CreateSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
IF NOT EXISTS (
		SELECT *
		FROM sys.schemas
		WHERE NAME = N'awlt'
		)
	EXEC ('CREATE SCHEMA [awlt] AUTHORIZATION [dbo]')
GO

IF NOT EXISTS (
		SELECT *
		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'awlt')
			AND type IN (N'U')
		)
	CREATE TABLE [awlt].[Account] (
		-- Columns Definition
		[AccountCodeAlternateKey] INT NOT NULL
		,[ParentAccountCodeAlternateKey] INT
		,[AccountDescription] NVARCHAR(50)
		,[AccountType] NVARCHAR(50)
		,[Operator] NVARCHAR(50)
		,[CustomMembers] NVARCHAR(300)
		,[ValueType] NVARCHAR(50)
		,[CustomMemberOptions] NVARCHAR(200)
		,[FlexRowEffectiveFromDate] DATETIME2(7) NOT NULL
		,[FlexRowEffectiveToDate] DATETIME2(7) NOT NULL
		,[FlexRowAuditId] BIGINT NOT NULL
		,[FlexRowRecordSource] VARCHAR(10) NOT NULL
		,[FlexRowSourceId] INT NOT NULL
		,[FlexRowIsCurrent] BIT NOT NULL
		,[FlexRowHash] VARCHAR(80)
		,[FlexRowHashKey] VARCHAR(40)
		-- Constraints
		,CONSTRAINT [PK_awlt_Account] PRIMARY KEY CLUSTERED (
			[AccountCodeAlternateKey] ASC
			,[FlexRowEffectiveFromDate] ASC
			) WITH (
			PAD_INDEX = OFF
			,IGNORE_DUP_KEY = OFF
			)
		ON "default"
		) ON "default"
		WITH (DATA_COMPRESSION = NONE)
GO

Data Flow Override

Override the Data Flow

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DataflowOverride" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<# 	CustomOutput.ObjectInherit = false; #>
<Dataflow Name="DFT - Load MySource" DelayValidation="true">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Transformations>
		<OleDbSource Name="OLE_SRC - MySource" ConnectionName="MySourceConnection">
			<DirectInput>SELECT * FROM [dbo].[MySourceTable]</DirectInput>
		</OleDbSource>
		<OleDbDestination Name="OLE_DST - MyTarget" ConnectionName="MyTargetConnection">
			<InputPath OutputPathName="OLE_SRC - MySource.Output" />
			<ExternalTableOutput Table="[dbo].[MyTargetTable]" />
		</OleDbDestination>
	</Transformations>
</Dataflow>
<# 	CustomOutput.OutputPathName = @"DFT - Load MySource.Output"; #>

Data Flow Properties

Configure logic that will be inject

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the Data Flow element will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DataflowProperties" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
<Expression ExternalProperty="SsisBufferTempStoragePath">@[User::SsisBufferTempStoragePath]</Expression>
<Expression ExternalProperty="SsisBLOBTempStoragePath">@[User::SsisBLOBTempStoragePath]</Expression>

Delete Detect Apply Dv Override

Override the SQL that is used by the Delete Detection Raw Data Vault process

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DeleteDetectionApplyDv" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<# CustomOutput.ObjectInherit = true; #>
<#
var psaIntegrationStage = "PSA";
var stagingTable = new TableObject(table, "STG", "STG");
var persistentTable = new TableObject(table, "PSA", "PSA");
var tableConfigurations = new TableConfigurations(table, "STG");
var psaTableConfigurations = new TableConfigurations(table, "PSA");
var enableEndDatePsa = tableConfigurations.EnableEndDatePsa;
var enableEndDateDv = tableConfigurations.DvEndDateSatellite;
var isCurrent = tableConfigurations.RowIsCurrent.PersistentStagingAttribute;
var deleteObjectNamePattern = tableConfigurations.DeleteObjectNamePattern;

var stagingConnection = stagingTable.Connection;
var stagingScopedName = stagingTable.ScopedName;
var stagingSchemaQualifiedName = stagingTable.SchemaQualifiedName;
var stagingObjectName = stagingTable.ObjectName;
var stagingQualifiedName = stagingTable.QualifiedName;

var deleteScopedName = deleteObjectNamePattern.Replace("@@this", stagingScopedName);

var sd = persistentTable.StartDelimiter;
var ed = persistentTable.EndDelimiter;
var persistentScopedName = persistentTable.ScopedName;
var persistentSchemaQualifiedName = persistentTable.SchemaQualifiedName;
var persistentQualifiedName = persistentTable.QualifiedName;
var persistentFullyQualifiedName = persistentSchemaQualifiedName == persistentQualifiedName ? $"{sd}{persistentTable.Database}{ed}.{persistentQualifiedName}" : persistentQualifiedName;

var qualifiedEffectiveFromDate = tableConfigurations.QualifiedRowEffectiveFromDate;
var qualifiedEffectiveToDate = tableConfigurations.QualifiedRowEffectiveToDate;
var qualifiedRowChangeType = tableConfigurations.QualifiedRowChangeType;
var indexPrimaryKeyColumns = table.GetIndexPrimaryKeyColumns(psaIntegrationStage);
var joinClause = "ON\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed + " = DEL." + sd + s + ed)
				.Aggregate((current, next) => current + "\nAND\t" + next);
var currentListJoinClause = "ON\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed + " = CL." + sd + s + ed)
				.Aggregate((current, next) => current + "\nAND\t" + next);
var	sqlPsaSelectPk = indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed)
				.Aggregate((current, next) => current + "\n\t\t," + next);
var whereClause = "WHERE\t" + indexPrimaryKeyColumns.Select(s => "DEL." + sd + s + ed + " IS NOT NULL").First();

var psaPkList = "\t" + indexPrimaryKeyColumns.Select(s => sd + s + ed)
				.Aggregate((current, next) => current + ", " + next);

var groupByClause = "\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed)
				.Aggregate((current, next) => current + "\n," + next);

var deleteTable = RootNode.Tables[deleteScopedName];
var deleteTableDatabaseQualifiedName = deleteTable.DatabaseQualifiedName;
var deleteTableSchemaQualifiedName = deleteTable.SchemaQualifiedName;

var mappedHubObject = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"HUB"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType))
		.Select(s => s.TargetColumn.RelatedItem.Object.RelatedItem).SingleOrDefault();
var mappedLinkColumns = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"LNK"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType));

var mappedSatelliteObjects = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"LSAT","SAT"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType))
		.Select(s => s.TargetColumn.RelatedItem.Object.RelatedItem).Distinct();

var sqlPsaMultiActiveKeys = stagingTable.Columns.Where(cl => cl.IsPrimaryKey == "Y" && cl.ChangeType == "MAK");
var sqlPsaCompareMultiActiveKeys = "";
var sqlPsaSelectMultiActiveKeys = "";
var sqlPsaSatSelectMultiActiveKeys = "";
foreach (var multiActiveKey in sqlPsaMultiActiveKeys)
{
	var sqlCompareMultiActiveKeys = multiActiveKey.GetSqlToBk(stagingTable.StartDelimiter, stagingTable.EndDelimiter, "PSA", "DV");
	sqlPsaSelectMultiActiveKeys += $"\n\t\t\t\t,{sqlCompareMultiActiveKeys}";
	sqlPsaSatSelectMultiActiveKeys += $"\n\t\t\t\t,{sqlCompareMultiActiveKeys} AS {sd}{multiActiveKey.GetColumnName()}{ed}";
	sqlPsaCompareMultiActiveKeys += $"\n\t\t\tAND\t{sqlCompareMultiActiveKeys} = {sqlCompareMultiActiveKeys.Replace("PSA.", "DEL.")}";
}
#>
<#	 foreach(var satellite in mappedSatelliteObjects) {
	var satellitePk = satellite.GetPrimaryKey();
	var satelliteObjectType = satellite.ObjectType;
	var hubPk = satellitePk.ReferenceColumn.RelatedItem;
	var hubObject = hubPk.Object.RelatedItem;
	var dvConnection = satellite.Connection.RelatedItem;
	var satelliteTable = new TableObject(satellite, "DV", "SAT");
	var hubTable = new TableObject(hubPk.Object.RelatedItem, "DV", "HUB");
	var satelliteConfigurations = new TableConfigurations(satellite, "SAT");

	var satelliteConfigurationsQualifiedRowIsCurrent = satelliteConfigurations.RowIsCurrent.SatelliteAttribute == "IGN" ? "" : satelliteConfigurations.QualifiedRowIsCurrent;

	var sqlSatMultiActive = satellite.GetSqlSatMultiActive(satelliteTable.StartDelimiter, satelliteTable.EndDelimiter);
	var sqlMultiActiveKeys = sqlSatMultiActive.Item1;
	var sqlCompareMultiActiveKeys = sqlSatMultiActive.Item2;
	var qualifiedSurrogateKey = satelliteTable.StartDelimiter + satellitePk.GetColumnName() + satelliteTable.EndDelimiter;

	var qualifiedHubSurrogateKey = hubTable.StartDelimiter + hubPk.GetColumnName() + hubTable.EndDelimiter;
	var linkHubJoinSk = "";
	var linkHubJoinBk = "";
	var linkPsaBk = "";
	var psaKeyJoin = "";
	var lsatJoinTest = "";
	var hubAlias = "";

	if (satelliteObjectType == "LSAT" && mappedHubObject == null){
		var linkHubColumns = mappedLinkColumns.Where(s => s.TargetColumn.RelatedItem != null && s.TargetColumn.RelatedItem.Object.RelatedItem == hubObject && s.TargetColumn.RelatedItem.IsPrimaryKey != "Y");
		var iHub = 1;
		foreach(var linkHubColumn in linkHubColumns){
			var targetLinkColumn = linkHubColumn.TargetColumn.RelatedItem;
			var targetLinkHubColumn = targetLinkColumn.ReferenceColumn.RelatedItem;
			if (targetLinkHubColumn == null) continue;
			var linkHubTable = new TableObject(targetLinkHubColumn.Object.RelatedItem, "DV", "HUB");
			//.Where(c => c.ReferenceColumn.RelatedItem != null && new []{"HUB"}.Contains(c.ReferenceColumn.RelatedItem.Object.RelatedItem.ObjectType) && c.Object.RelatedItem == hubObject);

			linkHubJoinSk += $"\n\t\tINNER JOIN {linkHubTable.QualifiedName} H{iHub}\n\t\t\tON\tHUB.{sd}{targetLinkColumn.GetColumnName()}{ed} = H{iHub}.{linkHubTable.PrimaryKeyQualifiedName}";
			linkHubJoinBk += $"\n\t\tAND\tH{iHub}.{linkHubTable.IntegrationKeyQualifiedName} = PSA.{sd}{linkHubColumn.GetColumnName()}{ed}";
			var linkPsaBkExpression = linkHubColumn.GetSqlToBk(sd, ed, "PSA", "PSA");
			linkPsaBk += $",{linkPsaBkExpression} AS {sd}{linkHubColumn.GetColumnName()}{ed}";

			lsatJoinTest += $"DEL.{sd}{linkHubColumn.GetColumnName()}{ed} = H{iHub}.{linkHubTable.IntegrationKeyQualifiedName} AND ";
			iHub ++;

		}
			lsatJoinTest = lsatJoinTest.Substring(0, lsatJoinTest.Length-4);


			linkPsaBk = linkPsaBk.Substring(1);
			var lsatHubTable = new TableObject(mappedHubObject, "DV", "HUB");
			//linkHubJoin = $"\n\t\tINNER JOIN {lsatHubTable.QualifiedName} LHUB\n\t\t\tON\tHUB.[{linkHubColumn.GetColumnName()}] = LHUB.[{lsatHubTable.GetPrimaryKey().GetColumnName()}]";
			//psaKeyJoin = $"\tON\tLHUB.[{lsatHubTable.BusinessKey.GetColumnName()}] = ";
		} else if (satelliteObjectType == "LSAT" && mappedHubObject != null) {
			var lsatHubTable = new TableObject(mappedHubObject, "DV", "HUB");
			linkHubJoinSk = $"\n\t\tINNER JOIN {lsatHubTable.QualifiedName} LHUB\n\t\t\tON\tHUB.{lsatHubTable.PrimaryKeyQualifiedName} = LHUB.{lsatHubTable.PrimaryKeyQualifiedName}";
			linkHubJoinBk = $"\n\t\tAND\tLHUB.{lsatHubTable.IntegrationKeyQualifiedName} = PSA.{stagingTable.IntegrationKeyQualifiedName}";
		hubAlias = "LHUB";
		} else {
			linkHubJoinBk = $"\n\t\tAND HUB.{hubTable.IntegrationKeyQualifiedName} = PSA.{stagingTable.IntegrationKeyQualifiedName}";
			hubAlias = "HUB";
		}
#>
<ExecuteSQL Name="SQL - Insert Deleted Into <#=satellite.ObjectName.MakeSsisSafe()#>" ConnectionName="<#=dvConnection.Name #>" ResultSet="None">
	<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
	<#	} #>
	<DirectInput>DECLARE @<#=satelliteConfigurations.RowEffectiveFromDateValue#> DATETIME2(7)
SELECT @<#=satelliteConfigurations.RowEffectiveFromDateValue#> = ISNULL(MAX(<#=tableConfigurations.QualifiedRowEffectiveFromDate#>), GETDATE()) FROM <#=deleteTableDatabaseQualifiedName#>

DECLARE @RowsInDelTable INT
SELECT @RowsInDelTable = CASE WHEN EXISTS (SELECT 1 FROM <#=deleteTableDatabaseQualifiedName#>) THEN 1 ELSE 0 END
IF @RowsInDelTable = 1
BEGIN
	BEGIN TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
		BEGIN TRY
		;WITH currentList AS
		(
			SELECT *
			FROM
			(
				SELECT
					<#=satellitePk.GetColumnName() #>
					,<#=qualifiedRowChangeType #>
					,ROW_NUMBER() OVER (PARTITION BY <#=satellitePk.GetColumnName() #> ORDER BY <#=qualifiedEffectiveFromDate #> DESC) as rn
				FROM <#=satelliteTable.QualifiedName #>
			) cur
			WHERE rn = 1 AND <#=qualifiedRowChangeType #> &lt;&gt; 'D'
		)
			INSERT INTO <#=satelliteTable.QualifiedName #>
					(<#=satelliteTable.InsertColumnsList.Replace("SAT.", "")#>)
			SELECT	 <#=satelliteTable.InsertColumnsList.Replace($"SAT.{qualifiedRowChangeType}", $"CONVERT(VARCHAR(1), 'D') AS {qualifiedRowChangeType}").Replace($"SAT.{satelliteConfigurations.QualifiedRowEffectiveFromDate}", $"@{satelliteConfigurations.RowEffectiveFromDateValue} AS {satelliteConfigurations.QualifiedRowEffectiveFromDate}")#>
			FROM	 <#=hubTable.QualifiedName #> HUB
			INNER JOIN <#=satelliteTable.QualifiedName #> SAT
			ON		HUB.[<#=hubPk.GetColumnName()#>] = SAT.[<#=satellitePk.GetColumnName() #>]<#=linkHubJoinSk #>
			AND EXISTS (
				SELECT 1 FROM (
						SELECT	 <#if (!string.IsNullOrEmpty(linkPsaBk)) {#><#=linkPsaBk #><#} else {#><#=stagingTable.IntegrationKey.GetSqlToBk(sd, ed, "PSA", "DV")#> AS <#=stagingTable.IntegrationKeyQualifiedName#>
		<#} #><#=sqlPsaSatSelectMultiActiveKeys #>
					FROM	<#=deleteTableDatabaseQualifiedName #> PSA ) AS DEL
				WHERE
				<#if ( !string.IsNullOrEmpty(linkPsaBk)) {#><#=lsatJoinTest #><#} else {#> DEL.<#=stagingTable.IntegrationKeyQualifiedName#> = <#=hubAlias #>.<#=stagingTable.IntegrationKeyQualifiedName#>
		<#} #><#=sqlCompareMultiActiveKeys.Replace("TGT.", "SAT.").Replace("SRC.", "DEL.") #>
			)
			INNER JOIN currentList CL ON CL.[<#=satellitePk.GetColumnName()#>] = SAT.[<#=satellitePk.GetColumnName() #>]

	COMMIT TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState	 );
		ROLLBACK TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
	END CATCH
END
	</DirectInput>
</ExecuteSQL>
<# } #>

Delete Detect Apply Psa Override

Override the SQL that is used by the Delete Detection Persistent Staging process

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DeleteDetectionApplyPsa" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	CustomOutput.ObjectInherit = true; #>
<#
var psaIntegrationStage = "PSA";
var stagingTable = new TableObject(table, "STG", "STG");
var persistentTable = new TableObject(table, "PSA", "PSA");
var tableConfigurations = new TableConfigurations(table, "STG");
var psaTableConfigurations = new TableConfigurations(table, "PSA");
var enableEndDatePsa = tableConfigurations.EnableEndDatePsa;
var enableEndDateDv = tableConfigurations.DvEndDateSatellite;
var isCurrent = tableConfigurations.RowIsCurrent.PersistentStagingAttribute;
var deleteObjectNamePattern = tableConfigurations.DeleteObjectNamePattern;

var stagingConnection = stagingTable.Connection;
var stagingScopedName = stagingTable.ScopedName;
var stagingSchemaQualifiedName = stagingTable.SchemaQualifiedName;
var stagingObjectName = stagingTable.ObjectName;
var stagingQualifiedName = stagingTable.QualifiedName;

var deleteScopedName = deleteObjectNamePattern.Replace("@@this", stagingScopedName);

var sd = persistentTable.StartDelimiter;
var ed = persistentTable.EndDelimiter;
var persistentScopedName = persistentTable.ScopedName;
var persistentSchemaQualifiedName = persistentTable.SchemaQualifiedName;
var persistentQualifiedName = persistentTable.QualifiedName;
var persistentFullyQualifiedName = persistentSchemaQualifiedName == persistentQualifiedName ? $"{sd}{persistentTable.Database}{ed}.{persistentQualifiedName}" : persistentQualifiedName;

var qualifiedEffectiveFromDate = tableConfigurations.QualifiedRowEffectiveFromDate;
var qualifiedEffectiveToDate = tableConfigurations.QualifiedRowEffectiveToDate;
var qualifiedRowChangeType = tableConfigurations.QualifiedRowChangeType;
var indexPrimaryKeyColumns = table.GetIndexPrimaryKeyColumns(psaIntegrationStage);
var joinClause = "ON\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed + " = DEL." + sd + s + ed)
				.Aggregate((current, next) => current + "\nAND\t" + next);
var currentListJoinClause = "ON\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed + " = CL." + sd + s + ed)
				.Aggregate((current, next) => current + "\nAND\t" + next);
var sqlPsaSelectPk = indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed)
				.Aggregate((current, next) => current + "\n\t\t," + next);
var whereClause = "WHERE\t" + indexPrimaryKeyColumns.Select(s => "DEL." + sd + s + ed + " IS NOT NULL").First();

var psaPkList = "\t" + indexPrimaryKeyColumns.Select(s => sd + s + ed)
				.Aggregate((current, next) => current + ", " + next);

var groupByClause = "\t" + indexPrimaryKeyColumns.Select(s => "PSA." + sd + s + ed)
				.Aggregate((current, next) => current + "\n," + next);

var deleteTable = RootNode.Tables[deleteScopedName];
var deleteTableDatabaseQualifiedName = deleteTable.DatabaseQualifiedName;
var deleteTableSchemaQualifiedName = deleteTable.SchemaQualifiedName;

var mappedHubObject = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"HUB"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType))
		.Select(s => s.TargetColumn.RelatedItem.Object.RelatedItem).SingleOrDefault();
var mappedLinkColumns = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"LNK"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType));

var mappedSatelliteObjects = table.MetadataModelDynamicObject.Columns
		.Where(e => e.Object.RelatedItem == table && e.TargetColumn.RelatedItem != null && new []{"LSAT","SAT"}.Contains(e.TargetColumn.RelatedItem.Object.RelatedItem.ObjectType))
		.Select(s => s.TargetColumn.RelatedItem.Object.RelatedItem).Distinct();

var sqlPsaMultiActiveKeys = stagingTable.Columns.Where(cl => cl.IsPrimaryKey == "Y" && cl.ChangeType == "MAK");
var sqlPsaCompareMultiActiveKeys = "";
var sqlPsaSelectMultiActiveKeys = "";
var sqlPsaSatSelectMultiActiveKeys = "";
foreach (var multiActiveKey in sqlPsaMultiActiveKeys)
{
	var sqlCompareMultiActiveKeys = multiActiveKey.GetSqlToBk(stagingTable.StartDelimiter, stagingTable.EndDelimiter, "PSA", "DV");
	sqlPsaSelectMultiActiveKeys += $"\n\t\t\t\t,{sqlCompareMultiActiveKeys}";
	sqlPsaSatSelectMultiActiveKeys += $"\n\t\t\t\t,{sqlCompareMultiActiveKeys} AS {sd}{multiActiveKey.GetColumnName()}{ed}";
	sqlPsaCompareMultiActiveKeys += $"\n\t\t\tAND\t{sqlCompareMultiActiveKeys} = {sqlCompareMultiActiveKeys.Replace("PSA.", "DEL.")}";
}
#>
<ExecuteSQL Name="SQL - Insert Deleted Into Persistent Staging" ConnectionName="<#=stagingConnection.Name #>" ResultSet="None">
	<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
	<#	} #>
	<DirectInput>DECLARE @<#=tableConfigurations.RowEffectiveFromDateValue#> DATETIME2(7)
SELECT @<#=tableConfigurations.RowEffectiveFromDateValue#> = ISNULL(MAX(<#=tableConfigurations.QualifiedRowEffectiveFromDate#>), GETDATE()) FROM <#=deleteTableSchemaQualifiedName#>

DECLARE @RowsInDelTable INT
SELECT @RowsInDelTable = CASE WHEN EXISTS (SELECT 1 FROM <#=deleteTableSchemaQualifiedName#>) THEN 1 ELSE 0 END
IF @RowsInDelTable = 1
BEGIN
	BEGIN TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
		BEGIN TRY
		;WITH currentList AS
		(
			SELECT *
			FROM
			(
				SELECT
					<#=psaPkList #>
					,<#=qualifiedRowChangeType #>
					,ROW_NUMBER() OVER (PARTITION BY <#=psaPkList #> ORDER BY <#=qualifiedEffectiveFromDate #> DESC) as rn
				FROM <#=persistentQualifiedName #>
			) cur
			WHERE rn = 1 AND <#=qualifiedRowChangeType #> &lt;&gt; 'D'
		)

		INSERT INTO <#=persistentQualifiedName #>
					(<#=persistentTable.InsertColumnsList#>)
		SELECT <#=persistentTable.InsertColumnsList.Replace($"PSA.{qualifiedRowChangeType}", $"CONVERT(VARCHAR(1), 'D') AS {qualifiedRowChangeType}").Replace($"PSA.{tableConfigurations.QualifiedRowEffectiveFromDate}", $"@{tableConfigurations.RowEffectiveFromDateValue} AS {tableConfigurations.QualifiedRowEffectiveFromDate}")#>
		FROM <#=persistentQualifiedName #> PSA
		INNER JOIN currentList CL <#=currentListJoinClause #>
		LEFT OUTER JOIN <#=deleteTableSchemaQualifiedName #> DEL
			<#=joinClause #>
		<#=whereClause #><# if (enableEndDatePsa){ #>
		UPDATE ODS
		SET	 <#=tableConfigurations.QualifiedRowEffectiveToDate#> = @<#=tableConfigurations.RowEffectiveFromDateValue#><# if (isCurrent != "IGN"){ #>,<#=tableConfigurations.QualifiedRowIsCurrent#> = 0
		<#} #>
		FROM	<#=persistentQualifiedName #> ODS
		INNER JOIN
		(
			SELECT	 <#=groupByClause#>
					,MIN(PSA.<#=tableConfigurations.QualifiedRowEffectiveFromDate#>) AS <#=tableConfigurations.QualifiedRowEffectiveFromDate#>
					,@<#=tableConfigurations.RowEffectiveFromDateValue#> AS <#=tableConfigurations.QualifiedRowEffectiveToDate#>
			FROM	 <#=persistentQualifiedName #> PSA
			LEFT OUTER JOIN <#=deleteTableSchemaQualifiedName #> DEL
				<#=joinClause #>
			WHERE PSA.<#=tableConfigurations.QualifiedRowEffectiveToDate#> = '9999-12-31'
				<#=whereClause #>
			GROUP BY <#=groupByClause #>
		) SRC
		<#=joinClause.Replace("PSA.", "ODS.").Replace("DEL.", "SRC.") #>
			AND	ODS.<#=tableConfigurations.QualifiedRowEffectiveFromDate#> = SRC.<#=tableConfigurations.QualifiedRowEffectiveFromDate#>
			AND	ODS.<#=qualifiedRowChangeType #> &lt;&gt; 'D'

		<#} #>

		COMMIT TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
		SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
		RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState	 );
		ROLLBACK TRANSACTION [<#=stagingTable.Id.Replace("-", "")#>]
	END CATCH
		END
	</DirectInput>
</ExecuteSQL>

Delete Detect Data Flow Override

Configure pipeline logic that will override the Delete Detect Data Flow

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DeleteDetectDataflowOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#
var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage);
var stageConnection = tableObject.Project.StageConnection.RelatedItem;
var sourceSsisSafeScopedName = tableObject.SsisSafeScopedName;
var columns = tableObject.Columns;
var primaryKeyColumns = columns.GetIndexPrimaryKeyColumns("DEL");
var sourceColumns = columns.Where(c => primaryKeyColumns.Contains(c.ColumnName));
#>
<#	CustomOutput.ObjectInherit = false; #>
<Dataflow Name="DFT - Detect Deletes ExtensionPoint" >
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Transformations>
		<!-- Insert Your Logic Here-->
	</Transformations>
<# 	CustomOutput.OutputPathName = @"DFT - Detect Deletes ExtensionPoint.Output"; #>
</Dataflow>

Delete Detect Raw File Override

Override the Raw File Format use by the Delete Detection process

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DeleteDetectRawFileOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#
var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage);
var stageConnection = tableObject.Project.StageConnection.RelatedItem;
var sourceSsisSafeScopedName = tableObject.SsisSafeScopedName;
var columns = tableObject.Columns;
var primaryKeyColumns = columns.GetIndexPrimaryKeyColumns("DEL");
var sourceColumns = columns.Where(c => primaryKeyColumns.Contains(c.ColumnName));
#>
<#	CustomOutput.ObjectInherit = false; #>
<RawFileFormat Name="rawDEL_<#=sourceSsisSafeScopedName#>" LogicalDisplayFolder="Raw">
	<Columns>
	<#	var i = 1;
		foreach(var sourceColumn in sourceColumns) {
			var applyDataTypeMappingStg = table.GetConfigurationValue("ApplyDataTypeMappingStg") == "Y" ? true : false;
			var bimlDataType = applyDataTypeMappingStg
				? sourceColumn.GetMappedDataTypeBiml()
				: sourceColumn.GetDataTypeBiml();
			#>
		<Column Name="<#=sourceColumn.ColumnName#>" <#=bimlDataType#> IndexPosition="<#=i #>" />
	<#	i ++; #>
	<#	} #>
	</Columns>
</RawFileFormat>

Delete Initialize Raw File Data Flow Override

Configure pipeline logic that will override the Delete Initialize Raw File Data Flow

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DeleteInitializeRawFileDataflowOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#
var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage);
var stageConnection = tableObject.Project.StageConnection.RelatedItem;
var sourceSsisSafeScopedName = tableObject.SsisSafeScopedName;
var columns = tableObject.Columns;
var primaryKeyColumns = columns.GetIndexPrimaryKeyColumns("DEL");
var sourceColumns = columns.Where(c => primaryKeyColumns.Contains(c.ColumnName));
#>
<#	CustomOutput.ObjectInherit = false; #>
<Dataflow Name="DFT - Initialise Raw File ExtensionPoint">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Transformations>
		<OleDbSource Name="OLE_SRC - <#=sourceSsisSafeScopedName#>" ConnectionName="<#=tableObject.SourceConnection.Name#>" CommandTimeout="0" ValidateExternalMetadata="true" >
			<DirectInput>
				SELECT [MyColumn] FROM	<#=tableObject.SourceSchemaQualifiedName#> ORDER BY [MyColumn]
			</DirectInput>
			<Columns>
				<Column SourceColumn="MyColumn" TargetColumn="MyColumn" SortKeyPosition="1" />
			</Columns>
		</OleDbSource>
		<RawFileDestination Name="RTF_DST - Initialise Raw Output File">
			<InputPath OutputPathName="OLE_SRC - <#=sourceSsisSafeScopedName#>.Output" />
			<FileFromVariableOutput VariableName="User.SourceRawFilePath"/>
		</RawFileDestination>
	</Transformations>
</Dataflow>

Get Parameter

Configure override to retrieve parameter values

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the process will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="GetParameter" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<# 	CustomOutput.ObjectInherit = true; #>
<#	var sourceConnection = table.GetSourceConnection();
	var sourceConnectionType = sourceConnection.ConnectionType;
	var sourceDatabase = table.GetSourceCatalog();
	var sourceScopedName = table.GetSourceScopedName(sourceConnection);
	var sourceQualifiedName = table.GetSourceQualifiedName(sourceConnection);
	var sourceSchemaQualifiedName = table.GetSourceSchemaQualifiedName(sourceConnection);
	var sourceSsisSafeScopedName = sourceScopedName.MakeSsisSafe();
	var sourceDisplayName = table.GetSourceDisplayName(sourceConnection);
#>
<#	if (table.GetParameters().Any()) { #>
	<ExecuteSQL Name="SQL - Get LastModified" ConnectionName="BimlCatalog" ResultSet="SingleRow">
		<Results>
			<Result Name="0" VariableName="User.CurrentModifiedDate" />
		</Results>
		<DirectInput>EXEC [ssis].[GetConfigVariable] '<#=sourceConnection.Name#>', '<#=table.Name#>.LastModifiedDate', 'LastModifiedDate', @VariableValue, @ExecutionID</DirectInput>
		<Parameters>
			<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
			<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
		</Parameters>
		<# CustomOutput.OutputPathName = @"SQL - Get LastModified.Output"; #>
	</ExecuteSQL>
<#	} #>

Lookup Cache

Configure a cache file for the Lookup SQL

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the source object to which the lookup cache will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the lookup cache will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="LookupCache" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<#	CustomOutput.ObjectInherit = false; #>
<#	var sourceConnection = table.GetSourceConnection();
	var sourceScopedName = table.GetSourceScopedName(sourceConnection);
	var sourceSsisSafeScopedName = sourceScopedName.MakeSsisSafe();
#>
<Dataflow Name="DFT - CACHE <#=sourceSsisSafeScopedName#>">
	<Transformations>
		<OleDbSource Name="OLE_SRC - Select <#=sourceSsisSafeScopedName#>" ConnectionName="<#=sourceConnection#>">
			<DirectInput>SELECT 	 SRC.[MyTableCode]
		,SRC.[MyTableKey]
		,SRC.[FlexRowHashType1] AS [lkpFlexRowHashType1]
FROM 	[dim].[MyTable] SRC
WHERE	SRC.[TenantCode] <> 'UNK'</DirectInput>
		</OleDbSource>
		<Cache Name="CT - <#=sourceSsisSafeScopedName#>" ConnectionName="MyTableCache">
			<InputPath OutputPathName="OLE_SRC - Select <#=sourceSsisSafeScopedName#>.Output" />
		</Cache>
	</Transformations>
</Dataflow>

Lookup Join

Configure a Join statement that will be added to the Lookup SQL

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the source object to which the lookup Join will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the lookup Join will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="LookupJoin" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
INNER JOIN [dbo].[MyJoinTable] LKP
	ON	SRC.[MyTableCode] = LKP.[MyTableCode]

Lookup Parameter

Configure a parameter for the Lookup SQL used by Lookup Cache

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the source object to which the lookup parameter will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the lookup parameter will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="LookupParameter" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<Parameter Name="Parameter1" VariableName="User.CurrentModifiedDate" />

Lookup SQL

Override the default generated Lookup SQL

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the source object to which the lookup SQL will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the lookup SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="LookupSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
SELECT	 SRC.[MyTableCode]
		,SRC.[MyTableKey]
		,SRC.[FlexRowHashType1] AS [lkpFlexRowHashType1]
FROM	[dim].[MyTable] SRC
WHERE	SRC.[TenantCode] <> 'UNK'

Override Alter SQL

Configure SQL that will Override the Alter SQL for a object

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideAlterSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<#	var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage); #>
ALTER TABLE [<#=tableObject.Schema#>].[<#=tableObject.ObjectName#>] ADD CONSTRAINT [DF_ep_<#=tableObject.Schema#>_<#=tableObject.ObjectName#>_FlexRowEffectiveFromDate] DEFAULT ('0001-01-01 00:00:00.000') FOR [FlexRowEffectiveFromDate]
GO

Override Initialize

Override the initializing of staging tables

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideInitialize" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>


<!-- This can be anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<#	var initializeConnection = sourceTable.GetInitializeConnection();
	var initializeSafeScopedName = sourceTable.GetInitializeScopedName(initializeConnection, sourceTable.Connection.RelatedItem.IntegrationStage).MakeFlexSafeName();
	var initializeSchemaQualifiedName = sourceTable.GetInitializeSchemaQualifiedName(initializeConnection, sourceTable.Connection.RelatedItem.IntegrationStage);
#>
<ExecuteSQL Name="SQL - Initialize <#=initializeSafeScopedName#>" ConnectionName="<#=initializeConnection.Name#>" ResultSet="None">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<DirectInput>DELETE FROM <#=initializeSchemaQualifiedName#> --WHERE 1=2</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - Initialize " + initializeSafeScopedName + ".Output"; #>
</ExecuteSQL>

Override Main

Completely override the main Data Flow (SEQC - Main)

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the override will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideMain" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This option is added for flexibility, but not recommended, as it will be disconnected from the BimlFlex patterns. -->
<Tasks>
	<Dataflow Name="DFT - Load MySource" DelayValidation="true">
		<Transformations>
			<OleDbSource Name="OLE_SRC - MySource" ConnectionName="MySourceConnection">
				<DirectInput>SELECT * FROM [dbo].[MySourceTable]</DirectInput>
			</OleDbSource>
			<OleDbDestination Name="OLE_DST - MyTarget" ConnectionName="MyTargetConnection">
				<InputPath OutputPathName="OLE_SRC - MySource.Output" />
				<ExternalTableOutput Table="[dbo].[MySourceTable]" />
			</OleDbDestination>
		</Transformations>
	</Dataflow>
</Tasks>

Override Merge

Override the Merge statement of target tables

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the override will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideMerge" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<!-- A scenario might be that you have an insert only fact and you might want to override the Merge to only insert and ignore updates. -->
MERGE [dim].[MyTable] AS TGT
USING [dim].[stage_MyTable] AS SRC
	ON TGT.[MyTableCode] = SRC.[MyTableCode]
--WHEN MATCHED
--	THEN
--		UPDATE
--		SET [MyTableCode] = SRC.[MyTableCode]
--			,[MyTableName] = SRC.[MyTableName]
--			,[FlexRowAuditId] = SRC.[FlexRowAuditId]
--			,[FlexRowHashType1] = SRC.[FlexRowHashType1]
--			,[FlexRowIsInferred] = SRC.[FlexRowIsInferred]
WHEN NOT MATCHED BY TARGET
	THEN
		INSERT (
			[MyTableCode]
			,[MyTableName]
			,[FlexRowAuditId]
			,[FlexRowHashType1]
			,[FlexRowIsInferred]
			)
		VALUES (
			SRC.[MyTableCode]
			,SRC.[MyTableName]
			,SRC.[FlexRowAuditId]
			,SRC.[FlexRowHashType1]
			,SRC.[FlexRowIsInferred]
			);

Override Primary Key SQL

Configure SQL that will Override the Primary Key SQL for a object

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverridePrimaryKeySql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<#	var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage); 
	var primaryKeySql = tableObject.IndexPrimaryKeyColumns.GetPrimaryKeySelectSql(tableObject.StartDelimiter, tableObject.EndDelimiter);
#>
CONSTRAINT [PK_ep_<#=tableObject.Schema#>_<#=tableObject.ObjectName#>] PRIMARY KEY CLUSTERED (<#=primaryKeySql#>, [FlexRowEffectiveFromDate])

Override SQL

Override the source query SQL generated by BimlFlex

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
SELECT DISTINCT [PropertyId]
	,[EnterpriseId]
	,CONVERT(INT, ISNULL([MemberId], - 1)) AS [MemberId]
FROM [dbo].[MyTable]

Override Unique Key SQL

Configure SQL that will Override the Unique Key SQL for a object

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="OverrideUniqueKeySql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<#	var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage); #>
CONSTRAINT [UK_ep_<#=tableObject.Schema#>_<#=tableObject.ObjectName#>] UNIQUE NONCLUSTERED (<#=tableObject.IntegrationKeyQualifiedName#>, [FlexRowEffectiveFromDate])

Package Add Connection Reference

Configure additional Connection references to the object package

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the connection will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PackageAddConnection" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- Note that the connection must be a valid connection defined within the BimlFlex metadata. Connections can be added manually if required. -->
<# 	CustomOutput.ObjectInherit = true; #>
<Connection ConnectionName="MY_SOURCE" />

Package Configurations

Add PackageConfigurations if you have unselected Use Project Deployment

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the configurations will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PackageConfigurations" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- Use PackageConfigurations to add a Configurations if you have unselected Use Project Deployment in the BimlStudio project.
When using Package Deployment instead of Project Deployment you might need to specify additional configurations. Configurations can also be added to the Batch using the PackageConfigurations combined with CustomOutput.ObjectInherit = true; -->
<# 	CustomOutput.ObjectInherit = true; #>
<#	var configurationPath = table.GetConfigurationValue("ConfigurationPath");
configurationPath = string.IsNullOrEmpty(configurationPath) ? @"C:\Varigence\Configurations" : configurationPath; #>
<PackageConfiguration Name="LOAD_MY_Configurations">
	<ExternalFileInput ExternalFilePath="<#=configurationPath#>\MY_BATCH_Configurations.dtsConfig" FileUsageType="ExistingFile" RelativePath="false" />
</PackageConfiguration>

Package Parameter

Configure parameter bound values to the package

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the parameter will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PackageParameter" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- The below example is adding a PackageParameter to the package. PackageParameter is normally combined with a ParameterBindings Extension Point -->
<# 	CustomOutput.ObjectInherit = true; #>
<Parameter Name="BatchId" DataType="String"></Parameter>
<Parameter Name="BatchInstanceId" DataType="String">0</Parameter>

Package Variable

Configure additional package variables or override default BimlFlex variables

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the variable will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PackageVariable" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- Variables can also be added to all the packages for the Batch using the PackageVariable combined with CustomOutput.ObjectInherit = true; -->
<# 	CustomOutput.ObjectInherit = true; #>
<Variable Name="TenantCode" DataType="String">UNK</Variable>
<Variable Name="CurrentModifiedDate" DataType="String" Namespace="User">1900-01-01</Variable>

Persistent Staging Initial Target Pipeline

Configure pipeline logic that will be injected into the source to staging package before the Persistent Staging Initial target destination node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PersistentInitialTargetPipeline" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<#	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn.Output"; #>
</DataConversion>

Persistent Staging Target Pipeline

Configure pipeline logic that will be injected into the source to staging package before the Persistent Staging target destination node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PersistentTargetPipeline" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<#	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn.Output"; #>
</DataConversion>

Post Create SQL

Configure SQL that will be executed after the Create DDL for a object

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PostCreateSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
ALTER TABLE [awlt].[Account] ADD CONSTRAINT [PK_awlt_Account] PRIMARY KEY CLUSTERED
(
	[AccountCodeAlternateKey] ASC,
	[FlexRowEffectiveFromDate] ASC
) ON [PRIMARY]
GO

Post Data Flow

Configure logic that will be injected after the main Data Flow

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the Data Flow element will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PostDataflow" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Set CurrentModifiedDate" ConnectionName="BimlCatalog" ResultSet="None">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<DirectInput>EXEC [ssis].[SetConfigVariable] 'MY_SRC', 'MY_SRC.dbo.EJTable.CurrentModifiedDate', 'CurrentModifiedDate', @VariableValue, @ExecutionID</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
		<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
	</Parameters>
</ExecuteSQL>

Post Process

Configure logic that will be injected after the main Object process

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the process will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PostProcess" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Set SnapshotDate" ConnectionName="BimlCatalog" ResultSet="None">
	<DirectInput>EXEC [ssis].[SetConfigVariable] 'LOAD_DATAMART', 'LOAD_DATAMART.SnapshotDate', 'SnapshotDate', @VariableValue</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.SnapshotDate" Length="-1" DataType="String" />
	</Parameters>
</ExecuteSQL>

Post Tasks

Configure logic that will be injected after all the Tasks in the main Data Flow

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PostTasks" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Set CurrentModifiedDate" ConnectionName="BimlCatalog" ResultSet="None">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<DirectInput>EXEC [ssis].[SetConfigVariable] 'MY_SRC', 'MY_SRC.dbo.EJTable.CurrentModifiedDate', 'CurrentModifiedDate', @VariableValue, @ExecutionID</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
		<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
	</Parameters>
</ExecuteSQL>

Pre Create SQL

Configure SQL that will be executed prior to the Create DDL for a object

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PreCreateSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
ALTER TABLE [awlt].[Account] DROP CONSTRAINT [PK_awlt_Account] WITH ( ONLINE = OFF )
GO

Pre Data Flow

Configure logic that will be injected before the main Data Flow

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the Data Flow element will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PreDataflow" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<!-- NOTE: You must add CustomOutput.OutputPathName with the last task to connect it with the Data Flow -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Get CurrentModifiedDate" ConnectionName="BimlCatalog" ResultSet="SingleRow">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Results>
		<Result Name="0" VariableName="User.CurrentModifiedDate" />
	</Results>
	<DirectInput>EXEC [ssis].[GetConfigVariable] 'MY_SRC', 'MY_SRC.dbo.EJTable.CurrentModifiedDate', 'CurrentModifiedDate', @VariableValue, @ExecutionID</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
		<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
	</Parameters>
<# 	CustomOutput.OutputPathName = @"SQL - Get CurrentModifiedDate.Output"; #>
</ExecuteSQL>

Pre Process

Configure logic that will be injected before the main Object process

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the process will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PreProcess" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Get SnapshotDate" ConnectionName="BimlCatalog" ResultSet="SingleRow">
	<Results>
		<Result Name="0" VariableName="User.SnapshotDate" />
	</Results>
	<DirectInput>EXEC [ssis].[GetConfigVariable] 'LOAD_DATAMART', 'LOAD_DATAMART.SnapshotDate', 'SnapshotDate', @VariableValue, @ExecutionID</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.SnapshotDate" Length="-1" DataType="String" />
		<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
	</Parameters>
</ExecuteSQL>

Pre Tasks

Configure logic that will be injected before all the Tasks in the main Data Flow

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PreTasks" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<!-- NOTE: You must add CustomOutput.OutputPathName with the last task to connect it with the Data Flow -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Get CurrentModifiedDate" ConnectionName="BimlCatalog" ResultSet="SingleRow">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Results>
		<Result Name="0" VariableName="User.CurrentModifiedDate" />
	</Results>
	<DirectInput>EXEC [ssis].[GetConfigVariable] 'MY_SRC', 'MY_SRC.dbo.EJTable.CurrentModifiedDate', 'CurrentModifiedDate', @VariableValue, @ExecutionID</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
		<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
	</Parameters>
<# 	CustomOutput.OutputPathName = @"SQL - Get CurrentModifiedDate.Output"; #>
</ExecuteSQL>

Reload From PSA Where SQL

Add a WHERE clause to the PSA reload package. Currently this cannot be parameterized.

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ReloadFromPsaWhereSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#	CustomOutput.ObjectInherit = false; #>
WHERE [MyColumn] = 1

Root Add

Add element to the root of the BimlFlex project

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the root elements will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="RootAdd" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- RootAdd is normally combined with the other extension points. An example will be adding a PreProcess that reads data and saves the output into a cache file to be used in a custom lookup in SourcePipeline.
The example below demonstrates adding the required connection and file format.-->
<# 	CustomOutput.ObjectInherit = true; #>
<#	var sourceConnection = table.GetSourceConnection();
	var sourceConnectionType = sourceConnection.ConnectionType;
	var sourceDatabase = table.GetSourceCatalog();
	var sourceScopedName = table.GetSourceScopedName(sourceConnection);
	var sourceQualifiedName = table.GetSourceQualifiedName(sourceConnection);
	var sourceSchemaQualifiedName = table.GetSourceSchemaQualifiedName(sourceConnection);
	var sourceSsisSafeScopedName = sourceScopedName.MakeSsisSafe();
	var sourceDisplayName = table.GetSourceDisplayName(sourceConnection); #>
<Connections>
	<CacheConnection Name="<#=sourceSsisSafeScopedName#>" PersistFileConnectionName="<#=sourceSsisSafeScopedName#>_File" RawFileFormatName="<#=sourceSsisSafeScopedName#>" />
	<FileConnection Name="<#=sourceSsisSafeScopedName#>_File" FilePath="C:\Varigence\<#=sourceSsisSafeScopedName#>.caw" />
</Connections>
	<FileFormats>
	<RawFileFormat Name="<#=ssisSafeScopedName#>">
		<Columns>
			<Column Name="MyTable_BK" DataType="AnsiString" Length="60" CodePage="1252" IndexPosition="1" />
			<Column Name="MyTable_SK" DataType="AnsiString" Length="40" CodePage="1252" />
		</Columns>
	</RawFileFormat>
</FileFormats>

Set Parameter

Configure override to publish parameter values

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the process will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SetParameter" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = true; #>
<#	var sourceConnection = table.GetSourceConnection();
	var sourceConnectionType = sourceConnection.ConnectionType;
	var sourceDatabase = table.GetSourceCatalog();
	var sourceScopedName = table.GetSourceScopedName(sourceConnection);
	var sourceQualifiedName = table.GetSourceQualifiedName(sourceConnection);
	var sourceSchemaQualifiedName = table.GetSourceSchemaQualifiedName(sourceConnection);
	var sourceSsisSafeScopedName = sourceScopedName.MakeSsisSafe();
	var sourceDisplayName = table.GetSourceDisplayName(sourceConnection);
#>
<#	if (table.GetParameters().Any()) { #>
	<ExecuteSQL Name="SQL - Set LastModified" ConnectionName="BimlCatalog" ResultSet="None">
		<DirectInput>EXEC [ssis].[SetConfigVariable] '<#=sourceConnection.Name#>', '<#=table.Name#>.LastModifiedDate', 'LastModifiedDate', @VariableValue, @ExecutionID</DirectInput>
		<Parameters>
			<Parameter Name="@VariableValue" VariableName="User.CurrentModifiedDate" Length="-1" DataType="String" />
			<Parameter Name="@ExecutionID" Direction="Input" DataType="Int64" VariableName="User.ExecutionID" />
		</Parameters>
		<# CustomOutput.OutputPathName = @"SQL - Set LastModified.Output"; #>
	</ExecuteSQL>
<#	} #>

Source Create SQL

Configure override Create DDL for a source object, like a view.

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the SQL will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceCreateSql" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<!-- The below example is adding View Create statement to the project. -->
IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[aw].[dimProduct]') AND type IN (N'V'))
	DROP VIEW [aw].[dimProduct]
GO
CREATE VIEW [aw].[dimProduct]
AS
WITH [CategoryCTE]([ParentProductCategoryID], [ProductCategoryID], [Name]) AS
(
	SELECT	 [ParentProductCategoryID]
			,[ProductCategoryID]
			,[Name]
	FROM	[aw].[SalesLT_ProductCategory]
	WHERE	[ParentProductCategoryID] IS NULL
	UNION ALL
	SELECT	 C.[ParentProductCategoryID]
			,C.[ProductCategoryID]
			,C.[Name]
	FROM	[aw].[SalesLT_ProductCategory] AS C
	INNER JOIN [CategoryCTE] AS BC
		ON	BC.[ProductCategoryID] = C.[ParentProductCategoryID]
)

SELECT	 p.[ProductID]
		,p.[Name] AS [ProductName]
		,p.[ProductNumber]
		,p.[Color]
		,p.[StandardCost]
		,p.[ListPrice]
		,p.[Size]
		,p.[Weight]
		,ccte.[ProductCategoryID]
		,ccte.[Name] as [ProductCategoryName]
		,pc.[ParentProductCategoryID]
		,pc.[Name] AS [ParentProductCategoryName]
		,p.[ProductModelID]
		,pm.[Name] AS [ProductModel]
		,pd.[Description] AS [ProductModelDescription]
		,p.[SellStartDate]
		,p.[SellEndDate]
		,p.[DiscontinuedDate]
FROM [aw].[SalesLT_Product] p
INNER JOIN [aw].[SalesLT_ProductModel] pm
	ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [aw].[SalesLT_ProductModelProductDescription] pmx
	ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [aw].[SalesLT_ProductDescription] pd
	ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]
INNER JOIN [CategoryCTE] ccte
	ON	ccte.[ProductCategoryID] = p.[ProductCategoryID]
INNER JOIN [aw].[SalesLT_ProductCategory] AS pc
	ON	pc.[ProductCategoryID] = ccte.[ParentProductCategoryID]
WHERE pmx.[Culture] = 'en'

Source Error Handling

Configure pipeline logic that will be injected on error of the source transformation node

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceErrorHandling" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
</DataConversion>

Source Error Handling Configuration

Configure Error Handling configuration logic that will be injected in the source transformation node

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceErrorHandlingConfiguration" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any valid ErrorHandling for the source component. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="IgnoreFailure" />

Source File Archive Override

Configure control flow logic that will override the SC_FILE_ARCHIVE script task call. You might also need to add a ProjectScriptFile.

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceFileArchiveOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<FileSystem Name="FST - YOUR FILE ARCHIVE" Operation="MoveFile" OverwriteDestination="true">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<VariableOutput VariableName="User.ArchivePath" />
	<VariableInput VariableName="User.SourceFullFilePath" />
	<# 	CustomOutput.OutputPathName = @"FST - YOUR FILE ARCHIVE.Output"; #>
</FileSystem>

Source File Loop Expression

Configure expressions that will be added to source ForEachFileLoop transformation

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the property will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceFileLoopExpression" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<Expression PropertyName="Directory">@[$Project::ImportPath]</Expression>

Source Override

Configure override for the Object source transformation node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the override will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
<#  var tableObject = new TableObject(table, table.Connection.RelatedItem.IntegrationStage);
	var sourceColumns = tableObject.SourceColumns; 
#>
<CustomComponent Name="CozyRoc_ParquetSource" ComponentTypeName="CozyRoc.ParquetSource" ContactInfo="Extracts data from Parquet file source.; COZYROC LLC; SSIS+; © 2006-2022 COZYROC LLC; All Rights Reserved; http://www.cozyroc.com/;0">
    <DataflowOverrides>
        <OutputPath OutputPathName="Parquet Source Output">
            <Columns>
            <#	foreach(var column in sourceColumns.Where(sc => sc.IsDerived != "Y")){ #>
				<Column ColumnName="<#=column.ColumnName #>" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
			<#	} #>
            </Columns>
        </OutputPath>
    </DataflowOverrides>
    <CustomProperties>
        <CustomProperty Name="Schema" DataType="System.String" UITypeEditor="CozyRoc.SqlServer.SSIS.TextDialogEditor, CozyRoc.SSISPlus.UI.2019, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=16cf490bb80c34ea" SupportsExpression="true" Description="JSON string representing the schema of the data file.">[
 <# var isFirst = true;
    foreach(var column in sourceColumns.Where(sc => sc.IsDerived != "Y")){ #>
    <#=isFirst ? ", " : "" #>{
        "Name" : "<#=column.ColumnName #>",
		"Type" : "<#=column.DataType.ToLower() #>"
	}
<#	} #>]</CustomProperty>
        <CustomProperty Name="ComponentObject" DataType="System.Null" TypeConverter="NOTBROWSABLE">
        </CustomProperty>
    </CustomProperties>
    <OutputPaths>
        <OutputPath Name="Parquet Source Output">
            <OutputColumns>
            <#	foreach(var column in sourceColumns.Where(sc => sc.IsDerived != "Y")){ #>
				<OutputColumn ExternalMetadataColumnName="<#=column.ColumnName #>" Name="<#=column.ColumnName #>" <#=column.GetDataTypeBiml() #> ErrorOrTruncationOperation="Conversion" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
			<#	} #>
            </OutputColumns>
            <ExternalColumns>
            <#	foreach(var column in sourceColumns.Where(sc => sc.IsDerived != "Y")){ #>
				<ExternalColumn Name="<#=column.ColumnName #>" <#=column.GetDataTypeBiml() #> />
			<#	} #>
            </ExternalColumns>
        </OutputPath>
        <OutputPath Name="Parquet Source Error Output" IsErrorOutput="true">
            <OutputColumns>
            <#	foreach(var column in sourceColumns.Where(sc => sc.IsDerived != "Y")){ #>
				<OutputColumn Name="<#=column.ColumnName #>" <#=column.GetDataTypeBiml() #> />
			<#	} #>
            </OutputColumns>
        </OutputPath>
    </OutputPaths>
    <Connections>
        <Connection Name="FILE" ConnectionName="CUSTOM_SRC" />
    </Connections>
    <# 	CustomOutput.OutputPathName = @"CozyRoc_ParquetSource.Parquet Source Output"; #>
</CustomComponent>

Source Parameter

Configure parameters for the Object source transformation node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the parameter will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceParameter" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
<Parameter Name="Parameter0" VariableName="User.LastModifiedDate" />
<Parameter Name="Parameter1" VariableName="User.CurrentModifiedDate" />

Source Pipeline

Configure pipeline logic that will be injected after the source transformation node

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourcePipeline" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn.Output"; #>
</DataConversion>

Source Property

Configure additional properties that will be added to source transformation node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the property will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceProperty" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
BindCharColumnAs="true" ExposeCharColumnsAsUnicode="false"

Staging Initial Target Pipeline

Configure pipeline logic that will be injected into the source to staging package before the Staging Initial target destination node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StagingInitialTargetPipeline" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<#	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn.Output"; #>
</DataConversion>

Staging Target Pipeline

Configure pipeline logic that will be injected into the source to staging package before the Staging target destination node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StagingTargetPipeline" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<#	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn.Output"; #>
</DataConversion>

Target Override

Configure override for the Object target transformation node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the override will be added
inputPath	String	Contains the output path of the preceding task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="TargetOverride" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>


<!-- NOTE: The type of target override must be consistent with the connection type. -->
<#	var targetConnection = table.GetTargetConnection();
	var targetScopedName = table.GetTargetScopedName(targetConnection);
	var targetSsisSafeScopedName = targetScopedName.MakeSsisSafe();
#>
<# 	CustomOutput.ObjectInherit = false; #>
<OleDbDestination Name="OLE_DST - <#=targetSsisSafeScopedName#>" ConnectionName="<#=targetConnection#>" MaximumInsertCommitSize="500000" BatchSize="500000" CheckConstraints="false">
	<InputPath OutputPathName="<#=inputPath#>" />
	<ExternalTableOutput Table="[dbo].[MyTable]" />
</OleDbDestination>

Target Pipeline

Configure pipeline logic that will be injected before the target transformation node

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
inputPath	String	Contains the output path of the preceding task
thread	String	Contains the thread number of the preceding task

Outputs

Name	Type	Description
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="TargetPipeline" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="inputPath" type="String" #>
<#@ property name="thread" type="String" #>


<!-- This can be any anything defined within the SSIS Data Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<DataConversion Name="DCV - Convert MyColumn<#=thread #>">
	<InputPath OutputPathName="<#=inputPath #>" />
	<Columns>
		<Column SourceColumn="MyColumn" TargetColumn="cnv_MyColumn" DataType="String" Length="100" />
	</Columns>
<# 	CustomOutput.OutputPathName = @"DCV - Convert MyColumn" + thread + ".Output"; #>
</DataConversion>

Target Post Process

Configure logic that will be injected after the main Object process using Source and Target table as parameter

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="TargetPostProcess" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Set SnapshotDate" ConnectionName="BimlCatalog" ResultSet="None">
	<DirectInput>EXEC [ssis].[SetConfigVariable] 'LOAD_DATAMART', 'LOAD_DATAMART.SnapshotDate', 'SnapshotDate', @VariableValue</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.SnapshotDate" Length="-1" DataType="String" />
	</Parameters>
</ExecuteSQL>

Target Pre Process

Configure logic that will be injected before the main Object process using Source and Target table as parameter

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="TargetPreProcess" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<!-- This can be any anything defined within the SSIS Control Flow. -->
<# 	CustomOutput.ObjectInherit = false; #>
<ExecuteSQL Name="SQL - Set SnapshotDate" ConnectionName="BimlCatalog" ResultSet="None">
	<DirectInput>EXEC [ssis].[SetConfigVariable] 'LOAD_DATAMART', 'LOAD_DATAMART.SnapshotDate', 'SnapshotDate', @VariableValue</DirectInput>
	<Parameters>
		<Parameter Name="@VariableValue" VariableName="User.SnapshotDate" Length="-1" DataType="String" />
	</Parameters>
</ExecuteSQL>

Target Property

Configure additional properties that will be added to target transformation node

Parameters

Name	Type	Description
table	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the property will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="TargetProperty" #>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<#	CustomOutput.ObjectInherit = false; #>
KeepNulls="true" KeepIdentity="false"