What We Learn

How to change load behaviour by changing script code.

Scenario

We are not happy about the fact that loading transactional data fails if the incoming data contains already loaded data, and we want to change that. Since this is a feature that is not parameterized (optimally, it should be), it is defined directly in the source code.

Stored procedures that perform the loading of the tables are based on templates. The template is read by a C# script that “fills in the blanks” in the template file. Changing default behaviour can be achieved by either editing the template file, the script file, or both.

Using Visual Studio Code, open the Workspace/Scripting folder in the D♯ Engine installation directory. Using Visual Studio Code’s Search function, search for the word WHEREIFNOTTRANSACTION to locate the files sqlserver pbody link.sql and CreateLoadLinkProcedureBody_Generic.cs. Open the two files in the Visual Studio Code editor.

The link tempate file sqlserver pbody link.sql looks like this:

alter procedure [%ETLSCHEMA%].[%LOADPROCEDURE%](@pLoadTime as datetime = null) as
%PROCHEADER%
begin
    set nocount on
	
	declare @taskGUID as uniqueidentifier = newid()
	declare @success as int = 0
	declare @fail as int = 0
	declare @msg as nvarchar(max)

	if (@pLoadTime is null)
		set @pLoadTime = getDate()

	exec [%ETLDATABASE%].[%ETLSCHEMA%].ETLTryTruncate @pLoadTime, '[%TARGETDATABASE%].[%TARGETSCHEMA%].[%TARGETOBJECT%]'
	
	exec [%LOGDATABASE%].[%LOGSCHEMA%].StartTask
		@taskGUID,
		@pLoadTime,
		'Link load', 
		'[%ETLDATABASE%].[%ETLSCHEMA%].[%WORKINGTABLE%]', 
		'[%TARGETDATABASE%].[%TARGETSCHEMA%].[%TARGETOBJECT%]',
		'[%ETLSCHEMA%].[%LOADPROCEDURE%]', 
		null, 
		'Loading %TARGETOBJECT% from %WORKINGTABLE%'

	begin try
		begin transaction

			insert into [%TARGETDATABASE%].[%TARGETSCHEMA%].[%TARGETOBJECT%]
			(
%TARGETFIELDS%
			)
			select %DISTINCT%
%SOURCEFIELDS%
			from
				[%ETLDATABASE%].[%ETLSCHEMA%].[%WORKINGTABLE%] as inData
%WHEREIFNOTTRANSACTION%

			set @success = @@ROWCOUNT

		commit
		set @msg = '%TARGETOBJECT% loaded from %WORKINGTABLE%'
	
	end try
	begin catch
		set @fail = 1
		rollback transaction
	
		set @msg = ERROR_MESSAGE()
	
	end catch

	exec [%LOGDATABASE%].[%LOGSCHEMA%].EndTask
	  @taskGUID,
	  @success,
	  @fail,
	  @msg
	
end

Notice the placeholder %WHEREIFNOTTRANSACTION%: it will be replaced by a where-clause, unless the link is a non-historized link. A section of the script file CreateLoadLinkProcedureBody_SQLServer.cs looks like this:

class CreateLoadLinkProcedureBody_SQLServer
{
    ...

    private String whereIfNotTransaction = 
	"where not exists (select 1 from [%TARGETDATABASE%].[%TARGETSCHEMA%].[%TARGETOBJECT%] where [%HASHNAME%] = inData.[%HASHNAME%])";
	
    ...

private String ReplacePlaceholders(String pBody, StoredProcedure pProcedure)
{  
        Tuple<string, string=""> columns = BuildLists();
      
        DataObject aTbl = _ProcFacade.GetTargetTable();
        Class aClass = new BITableFacade(aTbl).ImplementedObject as Class;
        String aWhere = whereIfNotTransaction;
        String aCheckTransaction = "";     
        
        if ((new BIClassFacade(aClass).IsTransaction))
        {
                aWhere = "";
                if (!App.SQLSyntax.EnforcesReferentialIntegrity)
                    aCheckTransaction = @"declare @existingRowCount as int
            select @existingRowCount = count(%HASHNAME%)
                from %ETLSCHEMA%.%WORKINGTABLE% as inData
                where exists (SELECT %HASHNAME% from %TARGETSCHEMA%.%TARGETOBJECT%)
           
            if @existingRowCount > 0
                RAISERROR ('ERROR: %ETLSCHEMA%.%WORKINGTABLE% contains duplicate hashes with %TARGETSCHEMA%.%TARGETOBJECT%, transaction data not loaded',11,1);
            ";
            }
       
        pBody = pBody.Replace("%WHEREIFNOTTRANSACTION%", aWhere);

    ...

</string,>

The red code lines are the ones handling this placeholder. If you want the procedure to “let in” rows that have not been loaded previously, and skip those that have, just comment out the following line like this:

        if ((new BIClassFacade(aClass).IsTransaction))
        //     aWhere = "";

In future deployments, all link loads will have the exists-check implemented in their load procedures.

So: make this change, save the .cs file, and restart D# Engine (script files will be read and compiled each time D# Engine is started). Then do this:

• Open the current load procedure etl.Load_lnk_AccountingEntry_from_work_FinMaster_RAW_ACCOUNTINGENTRY in an editor.
• Deploy the Accounting entry class.
• Open the newly installed version of etl.Load_lnk_AccountingEntry_from_work_FinMaster_RAW_ACCOUNTINGENTRY.
• Compare the two procedure versions.

Last, but not least, change these back:

        if ((new BIClassFacade(aClass).IsTransaction))
            aWhere = "";

Run Tutorial Scripts

Run the following tutorial script commands fron the Help -> Tutorials -> Intro Course -> Changing Load Behaviour menu, and inspect the results.

Next tutorial