Bulk Loading into MDS using SSIS

Each entity in SQL Server 2012 Master Data Services (MDS) will have it’s own staging table (stg.<name>_Leaf). Using this staging table, you can create, update, deactivate and delete left members in bulk. This post describes how to bulk load into an entity staging table and trigger the stored procedure to start the batch import process.

Staging Tables and Stored Procedures

The new entity based staging tables are an excellent feature in MDS 2012, and make it very easy to bulk load into MDS from SSIS. If you take a look at the SQL database used by your MDS instance, you’ll see at least one table in the stg schema for each entity. For this example I’ve created a Suppliers entity and I see a matching table called [stg].[Suppliers_Leaf]. If your entity is using hierarchies, you will have three staging tables (see BOL for details). If we expand the columns, we’ll see all of the attributes have their own columns, as well as some system columns that every staging table will have.

image

Each staging table will also have a stored procedure that is used to tell MDS that new data is ready to load. Details of the arguments can be found in BOL.

image

Import Columns

To load into this table from SSIS, our data flow will need to do the following:

  • Set a value for ImportType (see below)
  • Set a value for BatchTag
  • Map the column values in the data flow to the appropriate attribute columns

See the Leaf Member Staging Table BOL entry for details on the remaining system columns. If your Code value isn’t set to be generated automatically, then you’d also need to specify it in your data flow. Otherwise, the default fields can be safely ignored when we’re bulk importing.

The BatchTag column is used as an identifier in the UI – it can be any string value, as long as it’s unique (and under 50 characters).

MDS uses the same staging table for creating, updating and deleting entities. The ImportType column indicates which action you want to perform. The possible values are listed in the table below.

 

Value Description
0 Create new members. Replace existing MDS data with staged data, but only if the staged data is not NULL. NULL values are ignored. To change a string attribute value to NULL, set it ~NULL~. To change a number attribute value to NULL, set it to -98765432101234567890. To change a datetime attribute value to NULL, set it to 5555-11-22T12:34:56.
1 Create new members only. Any updates to existing MDS data fail.
2 Create new members. Replace existing MDS data with staged data. If you import NULL values, they will overwrite existing MDS values.
3 Deactivate the member, based on the Code value. All attributes, hierarchy and collection memberships, and transactions are maintained but no longer available in the UI. If the member is used as a domain-based attribute value of another member, the deactivation will fail. See ImportType 5 for an alternative.
4 Permanently delete the member, based on the Code value. All attributes, hierarchy and collection memberships, and transactions are permanently deleted. If the member is used as a domain-based attribute value of another member, the deletion will fail. See ImportType 6 for an alternative.
5 Deactivate the member, based on the Code value. All attributes, hierarchy and collection memberships, and transactions are maintained but no longer available in the UI. If the member is used as a domain-based attribute value of other members, the related values will be set to NULL. ImportType 5 is for leaf members only.
6 Permanently delete the member, based on the Code value. All attributes, hierarchy and collection memberships, and transactions are permanently deleted. If the member is used as a domain-based attribute value of other members, the related values will be set to NULL. ImportType 6 is for leaf members only.

When you are bulk loading data into MDS, you’ll use 0, 1 or 2 as the ImportType. To summarize the different modes:

  • Use 0 or 2 when you are adding new members and/or updating existing ones (i.e. doing a merge)
    • The difference between 0 and 2 is the way they handle NULLs when updating an existing member. With 0, NULL values are ignored (and require special handling if you actually want to set a NULL value). With 2, all values are replaced, even when the values are NULL.
  • Use 1 when you are only inserting new members. If you are specifying a code, then a duplicate value will cause the import to fail.

Package Design

You control flow will have at least two tasks:

  1. A Data Flow Task that loads your incoming data into the MDS staging table for your entity
  2. An Execute SQL Task which runs the staging table’s stored procedure which tells MDS to start processing the batch

image

Your data flow will have (at least) three steps:

  1. Read the values you want to load into MDS
  2. Add the BatchTag and ImportType column values (using a derived column transform)
  3. Load into the MDS staging table

image

As noted above, in your OLE DB Destination you’ll need to map your data flow columns to your member attributes (including Code if it’s not auto-generated), the BatchTag value (which can be automatically generated via expression), and the ImportType.

image

After the Data Flow, you’ll run the staging table stored procedure.

The first three parameters are required:

  1. The version name (i.e. VERSION_1)
  2. Whether this operation should be logged as an MDS transaction (i.e. do you want to record the change history, and make the change reversible?)
  3. The BatchTag value that you specified in your data flow

 

Additional resources:

10 thoughts on “Bulk Loading into MDS using SSIS”

  1. I also find it helpful, after all data has been staged, to call the stored procedure that validates the model. Otherwise, as I recall, new validation errors don't show up.

    I obtain the model information using a query like this (in an Execute SQL task)
    SELECT 1 AS User_ID, sv.Model_ID, sv.Name AS Version_Name, sv.ID AS Version_ID
    FROM mdm.viw_SYSTEM_SCHEMA_VERSION AS sv INNER JOIN
    mdm.viw_SYSTEM_SCHEMA_VERSION_FLAGS AS svf ON sv.VersionFlag_ID = svf.ID
    WHERE (sv.Model_Name = ?) AND (svf.Name = ?)

    The actual call to the stored procedure (another Execute SQL task — I often use the results of the first query for staging as well) looks like:
    EXEC mdm.udpValidateModel @User_ID = ?, @Model_ID = ?, @Version_ID = ?, @Status_ID = 1

    I didn't find a way to obtain the user ID other than to hard code it. It works for now.

  2. Hi
    I have a question about staging: i have two rows with same code, i want to load both of them into MDS UI, but MDS staging will not accept the same member code twice. How should I solve it?
    For instance:
    Code: 12345, Attribute1: 001
    Code:12345, Attibute2:002
    I want to load both rows together at the same time, they need to display the same code. Since it is the same code but come with different value.

    1. Code needs to be a unique value. You can set the entity to generate code values automatically and then map what you are using as codes, to a different attribute

  3. Hi, I'm very new to MDS, but have loaded some entities via staging tables and via the Excel add-in.
    I'm running into an issue that I have no idea why, and was wondering if you've seen this before (MSSQL 2012)

    I'm trying to update members in an entity in MDS via the staging table. I can successfully add new members, but any attribute updates to existing members (e.g. NAME) aren't populated to the entity view. The import process runs successfully with no errors.
    I've tried ImportType =0 and 2, neither works. When I set to 1, as expected I get an error. I also tried to update the code value using the NewCode column and that also does not get updated.

    I've set up staging data with an SSIS package, and also with direct T-SQL INSERT INTO statement.

    Using almost the same T-SQL INSERT statement for a test entity I just set up, I was able to get a new member added, and then to modify attributes for the new member in a second batch.

    Do you have any ideas why the updates would be ignored, or suggestions for things I can try?
    Thanks,

    1. Hello Johan
      I am running into exactly same issue. Did u figure out the solution. If yes could you please help me with that.

      Thanks
      VIBHA

  4. Hi,

    I'm afraid I couldn't have got into this article, because I the problem I have encountered is at the same beginning of bulk loading…Namely, I cannot see attributes (which I've got in my final table) in stg table (stg.name_Leaf). There are just default attributes ranging from ID to NewCode, but nothing more.

    I can say, that there should be almost 350 more of them, according to what I have in the final table.

  5. Hi Matt,

    I’ve done the same, and i found that we can get the UserID with this QUERY, given you know the name of the user.

    SELECT ID AS UserID
    FROM mdm.tblUser u
    WHERE u.UserName = ?

Leave a Reply

Your email address will not be published. Required fields are marked *