5 May 2017

Using SSIS To Load Data Into A Master Data Services Model

This article attempts to explain how to first create and configure MDS and afterwards create a simple SSIS package to load data from an independent source, load it to a staging table and finally load it to MDS.

MDS Overview:

>     Master Data Services is the SQL Services solution for master data management.

>     MDS has models that are the highest level container in the structure of master data. MDS makes possible the creation of these models to manage groups of similar data.

>     In turn a model contains one or more entities and entities contain members that are the data records. An entity is similar to a table.

Configuring the Master Data Services Database and Web Configuration:

>     This example was performed in a local machine, however it could be done in a remote machine:

>      In the Master Data Services Configuration Manager select the create database manager. Go through the wizard and connect to a SQL Server instance (locally or remotely), name your MDS database, configure an administrator account (only one is allowed) and finish.




>     This will create a new database called MDS_DEMO with tables, views, procedures etc. generated by MDS itself.

>     In the database configuration select your newly created database and go to the Web Configuration menu.

>     In the Web Configuration Menu create a new website (dropdown). Name both the website and the application pool and click ok



>     Finally click apply and check the Launch web application in browser option



>     This will launch the MDS website where the data can be configured, added and manipulated (Best to create a shortcut in your browser for the website to ease accessing it later).

>     In the website select the created model and go to System Administration menu



>     Create a new Entity:



>     In this example, we created an Entity called Human_Resources and it only has the two default MDS generated fields: Name and Code. We will load the data from the staging tables to this Entity.


Building the SSIS Package:

>     Now that MDS is configured, the package to load the data to the Staging tables and to MDS must be created:

>     In Microsoft Visual Studio, create a Business Intelligence Project – SSIS and rename it to something related to MDS. In this example, we will name it MDS_DEMO.

>     The following variables must be created in the package:​



>        strMDSEntityName will hold the value for the created entity in MDS where we will load the data

>        strMDSUser will hold the value for the domain and user configured in the MDS Server

>        strModelName will hold the value for the name of the MDS Model

>        The following connections must be created:




>        This is the connection to the external source



>        This is the connection to the MDS Database

>        In the Control Flow window drag the Execute SQL Task and rename it to Clear Staging Tables:



>        Connect this task to the created MDS database and add the following SQL Statement to it:



>        And configure the parameters as indicated below:



>        Drag a data flow task, rename it to Load Staging Tables and connect Clear Staging Tables task to it:



>        Double click the data flow task and it will open the data flow window

>        Drag an OLE DB Source tool and connect it to an external source that has the intended data to be loaded into MDS. In this case we will load two columns from the Employee table from AdventureWorks2014 database: BusinessEntityID and JobTitle



>        Add a derived column tool and rename it to Add User and Model Info. Connect the previous OLE DB Source to it.

>        Open the Add User and Model Info task and add the following derived column names:



>        ModelName has as an expression for the strModelName variable

>        UserName has as an expression for the strMDSUser variable

>        Drag another derived column tool to add the JobTitle and member information:



>        Drag an OLE DB Destination and connect it to MDS_DEMO. In the name of the table or the view choose: mdm.tblStgMember:



>        Configure the mappings like the example below:



>        Go back to the Control Flow window and add another Execute SQL Task. Connect the Load Staging Tables to it and rename it to Sweep Stage.



>        Connect the task to MDS_DEMO and add the following SQL Statement:



>        Configure the parameter mapping in the following way:



>        This task will get the data from the source into the model

>        Finally drag another Execute SQL Task and connect the Sweep Stage to it. Rename it to Validate Model

>        Connect the task to MDS_DEMO and add the following SQL Statement to it:



>        Add the following parameter mapping as the example below:



>        This task will validate all the loaded data in the MDS Model.

>        The final Solution should look like the following:



Checking the results

>     In order to check the results go the Human_Resources entity in MDS. In here, the data can be browsed or altered:



>     Another way of doing this is by installing the Excel Master Data Add-In and connecting to the MDS_DEMO Model:



>     With the add-in, data can be added or changed and afterwards published to the model.



>     Master Data Services is a very useful tool to create, manage and maintain consistent and accurate lists of master data. It improves compliance of business rules, reporting, profitability, decision-making and data quality. This article describes a very simple SSIS package to load data to MDS. It can be a starting point to more complex projects and/or provide assistance to those in need of Master Data Management.


    João Farinha