How to automatically export/backup the MDS model

I noticed that I was making backups of the MDS model manually, so it’s time to automate that in a basic but effective way 🙂
When using CMD, it’s always tricky to get the correct timestamp (which I’ll use in the output filename). If found a nice script on:

Normally when you would like to import or export/backup a MDS model, you’ll use these scripts:

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_20170804.pkg" -includedata

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy deployclone -package "E:\MDS\BimlMetaData_20170804.pkg" -service MDS1


To automate this, create a BATCH-file (*.bat) in your local ‘MDS backup directory’ (in my case ‘E:\MDS\’) and copy/paste the following script into it:



for /f "tokens=1-8 delims=.:/-, " %%i in ('echo exit^|cmd /q /k"prompt $D $T"') do (
for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
set dow=%%i
set mm=%%j
set dd=%%k
set yy=%%l
set hh=%%m
set min=%%n
set sec=%%o
set hsec=%%p

:: ensure that hour is always 2 digits

if %hh%==0 set hh=00
if %hh%==1 set hh=01
if %hh%==2 set hh=02
if %hh%==3 set hh=03
if %hh%==4 set hh=04
if %hh%==5 set hh=05
if %hh%==6 set hh=06
if %hh%==7 set hh=07
if %hh%==8 set hh=08
if %hh%==9 set hh=09

:: assign timeStamp:
:: Add the date and time parameters as necessary - " yy-mm-dd-dow-min-sec-hsec "

set timeStamp=%yy%%mm%%dd%_%hh%%min%%sec%

:: --------- TIME STAMP DIAGNOSTICS -------------------------

:: Un-comment these lines to test output

:: echo dayOfWeek = %dow%
:: echo year = %yy%
:: echo month = %mm%
:: echo day = %dd%
:: echo hour = %hh%
:: echo minute = %min%
:: echo second = %sec%
:: echo hundredthsSecond = %hsec%
:: echo.
:: echo Hello!
:: echo Today is %dow%, %mm%/%dd%.
:: echo.
:: echo Your timestamp will look like this: %timeStamp%
:: echo.
:: echo.
:: echo.
:: pause

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_%timeStamp%.pkg" -includedata


IMPORTANT: Make sure that you change the ‘MDS backup directory’ in the code above.

Now create a scheduled task (standard Windows functionality) that executes the BATCH-file and runs either on a specific time and/or system startup:

















Other handy scripts:
PowerShell: Delete old files based on a given retention period (days) #maintenance

Use PowerShell to script all database objects





SSIS 2016: Retrieving the ErrorDescription and ErrorColumnName

Since SQL Server 2016 there is a ‘built-in’ function to retrieve the actual column name of the column which caused the error. But there’s also a flaw, in case of a database constraint the ‘ErrorColumn’ (input parameter) will remain empty. So I’ve added an ELSE-statement to it in case it doesn’t receive a value:

How to set it up

1) Copy the script below

public override void Input0_ProcessInputRow(Input0Buffer Row)
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;

if (Row.ErrorColumn > 0)
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
Row.ErrorColumnName = "Database constraint :)";

2) Paste it into the script window and save it

3) Select both ‘ErrorCode’ and ‘ErrorColumn’ as input parameters

4) Now add two new output columns as shown below

In my case both output column are of data type string (DT_STR) with a size 250.

5) Add a Derived Column to the Data Flow after the Script Component and use the following SSIS expression to replace the original output value of ‘ErrorColumnName’

REPLACE( REVERSE( SUBSTRING( REVERSE( ErrorColumnName ),1,FINDSTRING( REVERSE(ErrorColumnName) ,”[“,1) – 1) ),”]”,” “)

(The original output value also contains the component’s name and we’re just interested in the column name itself)


There you go!
Many thanks to my colleague Gigi for the SSIS Expression 🙂