everyone!
We're using MS SQL Master Data Services to organize our enterprise master data and some of entities, we keep, consists of data, that we load from external sources almost as-is. We regularly update them using jobs or SSIS packages, placing data into staging tables ([stg].[<name>_Leaf]) and starting staging process using procedures, named as [stg].[udp_<name>_Leaf] as described in THIS and THIS topics about staging process in MDS.
Sometimes data, we import from some external source is presented as a flat table, just containing a set of rows, that we might want to reference in our other tables and then we load it and enjoy (in fact we place data into staging tables, call SP and let MDS proceed it in any, comfortable for server, moment, due to the main workload of staging process is running asynchronously, via Broker).
But there are a lot of other, ugly, but real-life cases, when data, that we load is presented as a tree, containing references to members, that we've not loaded yet and just going to place them into staging tables.
The problem is that in most cases we use automatic code creation function (and we cannot use not surrogate code), and we're not able to set member referencing's field value (where referenced's member code must be placed) to newly created member, before member is created and inserted into the base table and code is generated and set.
As I can see, we could resolve this problem, if we could reference staging member by staging table's ID, which is IDENTITY and assigned right after insert.
-OR-
If we could receive a callback from the staging process when data is placed into our base tables and codes are assigned. Then we'd calculate all references and update them (using the same staging process mech).
Currently, we use stupid not-very-elegant workaround, generating GUIDs and using them as Code value, when this scenario is.
Can anyone offer anything more enterprise? (:
