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.
Script | Source data | Main points of interest |
---|---|---|
Step 1: Reload corrected data | An error has been fixed on one row, and a new row has been added. | The link table load succeeds technically. The new row is loaded, but the corrected one isn’t. |