Loading data in MDS programmatically

As I demo MDS, there is one question that is asked pretty often – “Do I have to use the Import Wizard to pull the data from the Stage table into the MDS table?”.  Microsoft made this a little bit confusing so I am going to explain how Stage sync can be initiated programmatically. 

There are two steps that need to take place in order to load data in MDS.  First, one need to populate the stage table.  For every MDS entity, MDS will generate a stage table with the same name as the entity in the STG schema.  So if I create and Account entity, STG.Account table will be automatically generated for me. The STG.Account table will have all of the business attributes defined as well as several additional attributes necessary to define and control the import process.  Here is a link that describes how those additional attributes work.

After the stage table is loaded, a batch can be initiated to load the MDS entity.  That batch can be triggered from the Web UI, by going to the Integration Management Module.  However, most of us will want to do this programmatically.  Luckily, there is a stored procedure that can be executed to kick off the batch sync process. Here is the link to the help documentation for the stored procedure, but at the high level the syntax will look like this:

 EXEC [stg].[udp_account_Leaf]
      @VersionName = N’VERSION_1′,
@LogFlag = 1,
@BatchTag = N’MyBatch’

Note, and this is confusing, for each stage table there is a corresponding stored procedure in the STG schema that initiates the batch sync. So for Account entity, the stored proc is [stg].[udp_account_Leaf], and for a Location entity it would be [stg].[udp_location_Leaf].

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s