none
Import SQL Server Tables into MDS

    Question

  • I'd like to use the table definitions contained in an existing SQL database (2012) as input to MDM to kick start using MDM instead of having to key the information via UI.  Has anyone done this and if so would you please share how you did it?

    Thank you

    Bigguy365

    Sunday, November 03, 2013 4:06 AM

Answers

All replies

  • The easiest way I've seen so far is to select top 1 from your table, then copy the results and headers into Excel and then create the entity with the Excel add-in.

    Be sure to change numeric values to alphas in your spreadsheet before you create the entity if they will not end up being numeric in your entity.

    • Proposed as answer by BigGuy Wednesday, November 06, 2013 3:44 PM
    • Unproposed as answer by BigGuy Wednesday, November 06, 2013 3:44 PM
    • Proposed as answer by BigGuy Wednesday, November 06, 2013 3:44 PM
    Monday, November 04, 2013 2:34 PM
  • Check out the MdsModelImport tool that's part of the samples here http://mdsutilities.codeplex.com/.

    It will create MDS entities based on a SQL Server database, and is intended to jump-start your model and avoid a lot of manual entity creation.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by BigGuy Wednesday, November 06, 2013 3:44 PM
    Monday, November 04, 2013 3:06 PM
  • Thank you for this approach.  This will work when you already have the data populated an a database and we will use it.
    Monday, November 04, 2013 4:22 PM
  • Thank you David.

    I looked at the tool and I want to try it but I'm confused about one parameter.  It is the -MDSEndPoint (URL to MDS Web Service).  Where do I find the value of this URL?

    Thank you.

    Monday, November 04, 2013 4:31 PM
  • It's the MDS Web Service endpoint you will be hitting.  eg http://localhost/mds/Service/Service.svc if your MDS web app is deployed to http://localhost/mds and you are running MdsModelImport on the MDS server.

    David

    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 04, 2013 5:42 PM
  • Hey David

    I tried this yesterday and received the following error message:

    -------------------------------------
    Source Server: (local)
    Source Database: DMVSA
    MDS Web Service Endpoint Url: http://localhost/mds/Service/Service.svc
    Importing VSA schema.
    -------------------------------------

    The HTTP request is unauthorized with client authentication scheme 'Anonymous'.
    The authentication header received from the server was 'NTLM'.

    Our support area doesn't know what this means.  Can you shed some light on how to correct?

    Thank you

    Wednesday, November 06, 2013 12:30 AM
  • That error means that for some reason your Windows auth token is not being passed to the service.  I just tested on a fresh install of MDS and a download of the tool and I didn't get this. 

    Perhaps adding the web site to your trusted sites in IE would help.

    Can you browse to http://localhost/mds/Service/Service.svc and http://localhost/mds/

    And do you get a login prompt for either one?

    Daivd


    David http://blogs.msdn.com/b/dbrowne/



    Wednesday, November 06, 2013 3:04 PM
  • Problem Resolved.  We're using a cloud based environment and IE did not have NTLM either installed or activated (Sorry I'm not that familiar with IE authentication).  Regardless, thank you for your help.

    Wednesday, November 06, 2013 3:43 PM