search

MscrmTools / Javista.AttributesFactory

A, XrmToolBox plugin to manage attributes from an Excel spreadsheet
GNU General Public License v3.0
10 stars 4 forks source link

Using Template to migrate SQL Tables to Dataverse #7

Closed ozdemir-mehmet closed 1 year ago

ozdemir-mehmet commented 2 years ago

I've modified the supplied Template to allow it to be used to migrate SQL Server table definitions. It's pretty rough atm but I've been using it to bulk create SQL table definitions in Dataverse. The new template is Macro enabled (xlsm), as I needed some helper functions to space on capitals ie FirstName maps to the display name "First Name". Is there any interest in taking a look and improving it to the point where it could be included as an option in the plugin, hopefully, it will save someone else the time it took me to work everything out? I have some things hardcoded atm, like schema prefix, global optionset mapping etc, but I'm sure they could be moved to the ValidationData tab or in a new Config tab etc.

image

MscrmTools commented 2 years ago

Hi @ozdemir-mehmet, I'm not sure to understand why you updated the template. If you want to migrate SQL to dataverse, then just filling the template should have been enough, right? Migrating the data is another task that has nothing to do with this tool. Can you be more precise on what are the benefit of your changes?

ozdemir-mehmet commented 2 years ago

Say I have a SQL table with 80 fields, some are varchar, int, float, date, datetime, some nullable some not etc.. Using the template I would need to copy-paste 80 field names, then type the Display name (typically add a space), then map the datatypes to corresponding Dataverse datatype, set required drop-down, then tab across to the right section (eg text and put the field lengths in etc.

This can be quite a time-consuming task (but still better than using the UI)... In my version I just dump the schema definition from SQL Server (that the data you see to the right of the template), then my modified spreadsheet parses it, determines Display Name, Schema Name, Datatype, Length, Busines Required etc. The field in the main template are auto-populated based on the SQL schema details.

This isn't about importing data, it's about speeding up Dataverse table creation when you already have the schema defined in a SQL Table (and you're migrating a table across to Dataverse).

Does that make more sense?

MscrmTools commented 1 year ago

Seems to specific to me to be implemented by myself