Data Import Export Framework - Generate Auto Numbers
Views (2663)
dolee
11,279
Like
Share
ReportRecently I needed to setup ledger journal import using Data Import Export framework (DIEF). Even though there exists the Opening Balance entity, getting it done wasn't as straight forward as I thought. I've found some problem using the "Auto generated" flag in Source to Target mapping. The focus is on the DMFGenerateSSISPackage class.
The GetSchemaName method
First of all, there is the getSchemaName method. When dealing with auto number generation from number sequence, DIEF creates a SQL function (FN_FMT_NUMBERSEQUENCE) on the fly to do the number assignment and drops it afterwards. DIEF uses the getSchemaName method to get the schema name when creating this function. The original code uses "SELECT CURRENT_USER" but it can returns the AOS service account instead of "dbo", which is what is expected. Changed it to "SELECT SCHEMA_NAME()" will solve this.
Before moving on, let's define the import data. Let say we want to import 3 lines into the same journal. So I created a "Opening Balance" entity in a processing group. I modify the source-to-staging format, marking JournalNumber auto generated, and I also added the JournalName field in Query Criteria such that all 3 lines (with the same JournalName value) will be assigned the same JournalNumber.
The "Next" value in number sequence
Ok, so the import was fine and I see all 3 lines assigned the same journalNumber in staging. However, when looking at the Number Sequence, the "Next" number has increased by 3 instead of 1. Checking the code in the generateAutoNumbers method shows that this part is not handled properly. Luckily I can count the number of distinct journalNumber used in staging table and fix it.
"Free" numbers consideration for continuous number sequence
Finally, there's the "Free" number consideration for continuous number sequence. The original code simply assign one free number to one staging record until all free numbers are used up. That's not good enough. (e.g. say there is 1 free number in the journalBatchNumber number sequence, then the first line will be assigned the free number and the other two lines a new journal number.) Further editing needs to be done in generateAutoNumbersmethod to set this part straight as well.
The fixes required are quite straight forward so I won't bother to post the code here. Hopefully some of you will find this information helpful.
This posting is provided "AS IS" with no warranties, and confers no rights.
This was originally posted here.
*This post is locked for comments