WTFetch User Guide
Version 6.23
Introduction
WTfetch is a utility to import and export data from Whanau Tahi Navigator systems, but can be used against any Microsoft Dataverse or Dynamics 365 solutions.
It supports data export/import to/from csv files, and also can be used to create and maintain data extracts to a Microsoft SQL data warehouse (both cloud and on-premise).
The export option provides a way to backup data from cloud based CRM Online solutions to an on premise backup that can be accessed with or without a CRM system.
Operations
The following operations are supported. When running wtfetch one (and only one) of the following operations must be specified as the first parameter on the command line:
adminconsent
Opens a browser (using the default browser on the system) to grant consent for the app to access Navigator data. This can only be done by someone who has the Global Administrator or Cloud App Administrator role in your Azure AD.
Important
This must be done once before using WTfetch version 6.23 or later. This is a one time only operation; once administrative grants are run, this does not need to be done again.
export
Exports data from Whanau Tahi Navigator CRM to csv formatted files. When the command is complete, a file will be written called "exportsummary.txt" containing a list of entities exported and the number of records exported from each entity. This is in a csv format that can be opened in Microsoft Excel. This provides a control that can be used to validate the export and also provides a resource to compare any subsequent import operation against.
delete
Deletes data from the Whanau Tahi Navigator system. The list of entities (from the command line or from the config file) are processed in reverse order. This is done to maintain referential integrity where cascade delete is not enabled. Systemuser and businessunit table rows will not be deleted by this command.
Caution
This operation is permanent and cannot be undone.
To help mitigate against the possibility of accidentally running this command against the wrong organisation, an --auth option must be provided which acts like a password to ensure this is done deliberately.
import
Imports data from csv formatted files to Whanau Tahi Navigator. When the command is complete, a file will be written called "importsummary.txt" containing a list of entities exported and the number of records exported from each entity. This is in a csv format that can be opened in Microsoft Excel. This provides a control that can be used to validate the number of records imported. The file will include a summary of new records created as well as existing records updated, per entity.
sql
Builds data warehouse extract tables, and extracts data from Whanau Tahi Navigator to SQL extract tables
Useage
Follow the key operation verb, you provide additional arguments as defined below.
The general syntax is:
Wtfetch verb-command verb-command-options
e.g.,
wtfetch export -–orgkey 1 –-datafolder C:\wtfetch\mydata
In the following sections, some options offer a shorthand notation. E.g. instead of "--orgkey" you can use "-c" with the same result.
Log Files
Each operation writes its own log file and also creates a summary file that can be used to validate the number of records imported, exported, or deleted.
The export log files are: exportlog.txt and exportsummary.csv
The delete log file is: deletesummary.csv
The import log files are: importlog.txt and importsummary.csv
There is currently no logfile for the sql operation.
Log files are written to the location specified by –datafolder or else the current working directory if –datafolder is not specified.
Common Options (Connection and Setup)
These options can be used with all command verbs.
--config|-c config_filename
Specifies the xml config file (default=config.xml). If the config file is not specified, the program will check for the config.xml in these locations (in order): the datafolder location, the current working directory, the program executable folder.
--orgkey|-n orgkey
Specifies the organisation key. Orgkey is an integer value defined in the config file. If orgkey is provided, orgname is not required. If this option is omitted, -n 1 is assumed.
--orgname|-o orgname
Specifies the organisation name. Orgname is a string defined in the config file. If orgname is provided, orgkey is not required.
--entitylist|-e entitylist
Specifies a comma separated list of entity logical names to be exported or imported. If provided, the value here will over-ride the entity list specified in the config file. Entity names used here (and in the config.xml file) are the same as the logical entity names in the CRM system. A list of these names can be viewed using the CRM customization option, or using the XRMToolBox Metadata Browser or Metadata Document Generator tools.
--verbose|-v
Provide verbose diagnostic output to the console.
--datafolder path
Specifies an output folder for data and log files. If not specified, the current working directory will be used.
Import Options
The following options apply to the _ import _command verb:
--assumevalidguids|-l
This will force wtfetch to assume that all guids for lookup references point to valid fields. If this does not hold true, errors will occur and the data import will most likely fail. This option is not recommended in normal scenarios.
--dontupdateimportsequencenumber
By default, the row number from the source csv file will be written to the import sequence number field of the target entity. This helps to identify the source of an imported record. Use this option to prevent this from occurring. The sequence number is only updated for new records created during the import. If an existing record is updated, its import sequence number is not modified.
--getattachments
Wtfetch will attempt to read and encode attachments from the ATTACHMENTS directory. When this option is specified, attachments files from the folder will replace any encoded attachment values from csv files. This is useful if new attachments are being loaded and/or if exist ones are being replaced. The attachment files in this folder must be named as follows:
GUID\__filename.filetype_
Where GUID is in the format:
NNNNNNNN-NNNN-NNNN-NNNN-NNNNNNNNNNNN
And is not case sensitive, e.g.
BE613D67-4AE6-4418-8271-43C6A427711B
So an example file name would be:
BE613D67-4AE6-4418-8271-43C6A427711B_SignedConsent.pdf
The GUID must match the primary GUID of a record in the attachment, or activitymimeattachment table. This record determines the attachment's linkage to a note, activity or email message.
If this option is used to import new attachments, tools such as https://www.guidgenerator.com/ may be useful to generate sets of new Guids which can then be used in the attachment csv file and the files themselves.
Export Options
The following options apply to the _ export _ command verb:
--activerecordsonly
-a
Export active records only. Inactive records will be ignored if this option is specified.
--utcdate
-u
Output dates in UTC round-trip format. Changes the format for dates to UTC round trip. Useful if transferring data between systems with different time zones.
--getattachments
Writes attachments to a folder named ATTACHMENTS in the output folder. If the ATTACHMENTS subfolder does not exist, it will be created. Note: Attachments exported using this option can be opened and viewed in their original format (e.g. pdf, word document, excel, etc).
--protectnames
Names stored in the contact entity will be replaced by a sequence number during the import. This is provided if data is being loaded to a system for reporting purposes, but it is desired to prevent the casual observer from identifying an individual. Note that this does not offer complete protection: individuals may still be identified through other means, such as NHI, address, or other data correlation.
--setowner
By default, the system will attempt to reattach records to the owner specified in the csv file. Where this cannot be done using either the owner guid or name, the record will be owned by the user that is running the process. The --setowner option may be used to override this, and all records will be owned by the user specified in this option. When specified the user, use the full name of the user as it appears in the Display Name of the user in the target CRM system. Double quotes will be needed if the user name contains spaces.
--updateviews
By default, if the entity savedquery (views) is imported, it will NOT updated any existing queries; it will only create new ones. Use this option to force the import of savedquery to update existing views. Use this with caution: updating existing views can cause errors which are not easily fixed due to changes in object ids that occur from one CRM instance to the next.
Delete Options
Warning: This command is irreversible.
--auth authorisation_password
Provides an authorisation password to carry out this command. This is required. Failure to provide the correct authorisation password will cancel the operation.
The authorisation password is a safety mechanism to prevent the accidental deletion of data and ensure that the target system is in fact the correct target system (because each target system has its own authorisation password). The authorisation password is the organisation name (as specified in the config.xml file), converted to lower case, and then adding 1 position to each letter of the organisation name forward one character. For example, 'a' becomes 'b', 'b' becomes 'c', and so on. If a character is 'z', this becomes 'a', and if a numeric character is '9' this becomes '0'.
Example: If the organisation name is "demo9" then the authorisation password is "efnp0" (d->e, e->f, m->n, o->p, 9->0).
SQL Options
These options apply to the SQL command line verb.
--generateddl
-g
Generates a DDL script for the entity list (provided either by the config file or the –e option)
--removeorgdata
-x
Remove org data. All data for the organisation (specified with –n or –o) will be deleted from the target SQL database.
--droptables
-d
Drop tables. All tables for the entity list will be dropped from the target SQL database.
--executeddl
-r
Execute DDL. The DDL script will be generated and executed on the target SQL database.
--truncatetables
-t
Truncate tables. All tables for the entity list will be truncated in the target SQL database. Faster than –d.
--extract
-h
Transfer to SQL. Data is export from CRM and loaded into the target SQL database. Tables must already exist and should have been previously created with the –r option or by running the script generated by the –g option.
Examples
Generates DDL, Drops existing tables, rebuilds new tables, transfers data, uses org #1 by default
Wtfetch sql -g -d -r –h
Exports Data from an organisation to SQL
Wtfetch sql –n1 --extract
Exports Data from an organisation to csv files
Wtfetch export –n1 –v
Imports data to an organisation from csv files
Wtfetch import –n1 –v
Generates an encrypted password for use in the config file
Wtfetch encrypt --username my.user@whanautahi.com --password myPassword1
Import/Export Order
The order in which entities are listed for export is not significant.
When importing data, the order is significant. Where data being imported has lookup fields that refer to other records, those must be imported first. For example, Ethnicity and Iwi must be imported before attempting to import contacts (unless the ethnicity and iwi data are already present in the target system). If the --delete option is provided on import, then deletes are carried out in the reverse order of the list. As for importing, this is necessary to preserve referential integrity during the delete process.
Config File
The configuration file, config.xml, provides information on how to connect to the SQL database (if applicable) and to the source/target CRM system. It also provides a list of entities to be exported or imported, and the order in which this should occur (which is important for import operations).
When wtfetch runs, it will first check for a config.xml file in the folder specified by --datafolder. If this does not work, it will then try the current working directory, and if still unsuccessful it will try the program directory before failing.
Other Notes
- If systemuser is imported, new users are created with the default business unit if no matching business unit can be found. Note that users of Microsoft CRM Online may not be able to import users at all, as users must be added and licensed using the Office 365 portal. Note that importing users is not usually enough: you still need to assign a security roles and configure mail settings and possibly other settings for each user.
- Where possible, record ownership is preserved. If the owner of a record does not exist in the target (import) CRM system, then the ownership will be set to the user that the wtfetch authenticated as. Owner will be matched by Guid and of no match is not found, it will try to locate a corresponding user or team record with the same name. Note that if a record is owned by a team, only a match team will succeed – it will not try to replace a team owner with a user owner, unless an existing team cannot be located.
- The createdon date will be preserved (using the overridencreatedon field).
- The importsequencenumber is only updated for new records created during an import, not for existing records updated. If the –dontUpdateImportSequenceNumber option is specified, the importsequencenumber field will not be updated.
- When importing records that are not active, the record is first created in the default active state, and then the status is changed to the state and statuscode values provided in the source csv file. It is possible that this will invoke workflows or other plugins. If there is a risk of this occurring and it is not wanted, you must ensure that they are disabled prior to running the import, and re-enabled afterwards.
- The owner of a record is set by wtfetch based on the owner in the source csv file. For this to succeed, the user credentials used for the import should have an appropriate level of create permission. E.g. they should have organisation permissions on create of relevant entities, or sufficient permission levels to allow them to set an owner within the restricted group – e.g. if the create permission is business unit only, then all records imported should have owners that are in the same business unit as the import user.
Requirements
The host running wtfetch must have Windows Identity Foundation installed
Authentication credentials for CRM connections are stored in the Windows Credentials Store. For each CRM organisation, you need to create a generic windows credential with the name:
WhanauTahi_Navigator:Fetch:URL
Where URL is the URL of the crm organization, e.g. https://myorg.crm6.dynamics.com.
The username and password provided should have the necessary security role assigned to read and if necessary update records.
SETUP (version 6.20)
The following needs to be done (this is a one-off operation):
Grant adminconsent for wtfetch to access your whanau tahi data.
Create an app user in the Dataverse Environments that wtfetch should have access to, and give it a security role that has permission to read the required data tables.
Grant Admin Consent
Before using wtfetch, admin consent must be granted. You can do this by running the following from the command line:
Wtfetch adminconsent
This will open the adminconsent grant in the system default browser. You must login using an account with Global Admin or Cloud App Administrator roles in your Azure AD tenant.
This only needs to be done once.
Alternatively, you can past the following URL into your browser (while logged in with a Global Admin or Cloud App Administrator account):
The Whanau Tahi home page will be displayed if consent is successfully granted.
Note: In some cases the consent may take 1-2 minutes to take effect. To avoid issues, wait 2 minutes before proceeding to the next step.
Create App User
Open the Power Apps Admin Portal (https://admin.powerplatform.microsoft.com)
Click "Environments"
Select the environment that you want to extract data from
Click "Settings"
Click "Users + Permissions"
Click "Application Users"
Check that "Whanau Tahi WTFetch" does not appear in the list. If it does, validate that the App ID is 61e12712-4094-485a-b9d5-2927a9e1a52b. If it is already present with the correct App ID, check that it has the business unit and security role assigned, and skip the remaining steps.
Click "+ New app user"
Click "+ Add an app"
Paste 61e12712-4094-485a-b9d5-2927a9e1a52b into the "Search by App Name or Id" box
Whanau Tahi WTFetch will appear in the list. Select it.
Select the business unit for the app user to belong to. Normally this will be the root business unit.
Select an appropriate security role. The secureity role must have read access to the data that you will extract using wtfetch.
Press CREATE
Update config.xml
If you have upgraded to wtfetch 6.20 , you will need to update your config.xml
To do this, you will need to obtain the tenantid for your organisation, and the organisation id for each of the Whanau Tahi Navigator environments that wtfetch will need to access.
You can obtain the Tenant Id by going to https://portal.azure.com and navigating to Azure Active Directory. The Overview will display the Tenant ID. It is a 32-character string like this (this is an example only and is not a valid tenant id):
a77f5674-8427-4c98-a867-095c63531369
You obtain the Organisation Id for an Environment by going to the power platform admin portal (https://admin.powerplatform.microsoft.com) and click on "Environments". Then select the target environment. The Organization Id is displayed. It is usually a 32-character string similar to the tenant id, but in rare cases it is a shorter string of text.
The tenant id is the same for all your Navigator Connections, but the Organisation Id is different for each Navigator instance.
In config.xml, the connection string should be updated to the following structure:
<connection orgkey="<yourOrgNumber>" orgname="<yourOrgName>" url="https://<yourOrgEnvironmentUrl " orgid="<yourOrgId" tenantid="yourTenantId" />
All fields are required.
The following example configures connections to two Navigator systems:
<connection orgkey="1" orgname="Demo1" url="https://demo1.crm6.dynamics.com" orgid="b16526d8-fb89-4ea4-b87f-7c453165e102" tenantid="4ba53ea2-4b0e-47e4-83b2-e50f518c45c3" />
<connection orgkey="2" orgname="Demo1test" url="https://demo1test.crm6.dynamics.com" orgid="fba319ff-2a9e-44a7-aa24-b18f625baae1" tenantid="4ba53ea2-4b0e-47e4-83b2-e50f518c45c3" />
Sample config.xml File
<?xml version="1.0" encoding="utf-8" ?>
<wtfetch>
<datawarehouseconnections>
<datawarehouse dbid="1" connectionString="Persist Security Info=false;Server=WTL-SQL-PARA;Initial Catalog=WTN_DW;Integrated Security=true;" />
</datawarehouseconnections>
<crmconnections>
<connection orgkey="1" orgname="MyOrg" url="https://myorg.crm6.dynamics.com" orgid="232FF715-6688-405F-9911-790DE6A1E4E2" tenantid="3494E26A-DC9C-4AE0-9F1F-79C63E1949ED" />
<connection orgkey="2" orgname="MyOrgTest" url="https://myorgtest.crm6.dynamics.com" orgid="1DC15F52-010D-436D-9E7C-00CF957EAE27" tenantid="3494E26A-DC9C-4AE0-9F1F-79C63E1949ED" />
</crmconnections>
<entityextractlist>
<Entityname="systemuser" />
<entityname="mag_activitycode" />
<entityname="mag_agegroup" />
<entityname="mag_gatheringplace" />
<entityname="mag_collectivegroup" />
<entityname="mag_ethnicity" />
<entityname="mag_levelofeducation" />
<entityname="wtl_autonumber" />
<entityname="mag_employmentstatus" />
<entityname="mag_referralcode" />
<entityname="mag_referraltype" />
<entityname="mag_contractservicetype" />
<entityname="mag_contracttype" />
<entityname="mag_crisistype" />
<entityname="mag_declinedreason" />
<entityname="territory" />
<entityname="mag_consenttype" />
<Entityname="mag_tribe"filename="mag_tribe-iwi"filter='<condition attribute="mag_parenttribeid" operator="null" /><condition attribute="statecode" operator="eq" value="0" />'/>
<Entityname="mag_tribe"filename="mag_tribe-hapu"filter='<condition attribute="mag_parenttribeidname" operator="like" value="%%" /><condition attribute="statecode" operator="eq" value="0" />'/>
<entityname="mag_externalcontacttype" />
<entityname="site" />
<entityname="mag_areablock" />
<entityname="mag_sector" />
<entityname="mag_analysiscode" />
<entityname="mag_domain" />
<entityname="mag_organisationtype" />
<entityname="mag_reasonforreferral" />
<entityname="mag_settingcategory" />
<entityname="mag_setting" />
<entityname="mag_primhdteam" />
<entityname="mag_primhdcontrol" />
<entityname="mag_organisationinterfaceentity" />
<entityname="mag_interfacesetting" />
<entityname="mag_interfacesession" />
<entityname="mag_interfacelog" />
<entityname="mag_domain_analysiscode" />
<entityname="mag_reportingperiod" />
<entityname="mag_fundingcontract" />
<entityname="mag_fundingcontractpayment" />
<entityname="mag_contractservice" />
<entityname="mag_subservice" />
<entityname="mag_profiletemplate" />
<entityname="mag_grouping" />
<entityname="account" />
<entityname="mag_familygroup" />
<entityname="contact"filename="contact-gp"filter='<link-entity name="mag_externalcontacttype" from="mag_externalcontacttypeid" to="mag_externalcontacttypeid" alias="ab" ><filter type="and" ><condition attribute="mag_name" operator="eq" value="GP" /></filter></link-entity>' />
<entityname="contact"filename="contact-all" />
<entityname="customeraddress"filename="customeraddress-3up"filter='<condition attribute="addressnumber" operator="ge" value="3" />'/>
<entityname="mag_mag_familygroup_contact" />
<entityname="mag_profile" />
<entityname="mag_contact_mag_tribe" />
<entityname="mag_contact_mag_ethnicity" />
<entityname="mag_individualconsent" />
<entityname="mag_assessmenttemplate" />
<entityname="mag_answer" />
<entityname="mag_question" />
<entityname="mag_eligibilityrule" />
<entityname="mag_benchmark" />
<entityname="lead" />
<entityname="mag_programmetemplate" />
<entityname="mag_programmesessiontemplate" />
<entityname="mag_programme" />
<entityname="mag_programmesession" />
<entityname="mag_attendee" />
<entityname="mag_asm_whakaahua" />
<entityname="mag_assessment" />
<entityname="mag_referral" />
<entityname="mag_medtechreferral" />
<entityname="mag_plan" />
<entityname="mag_plangoal" />
<entityname="mag_planreview" />
<entityname="mag_outcome" />
<entityname="mag_outcome_analysiscode" />
<entityname="mag_outcomestep" />
<entityname="mag_timespent" />
<entityname="mag_outcomestep_analysiscode" />
<entityname="activityparty" />
<entityname="activitypointer" />
<entityname="email" />
<entityname="mag_note" />
<entityname="appointment" />
<entityname="fax" />
<entityname="letter" />
<entityname="phonecall" />
<entityname="task" />
<entityname="recurringappointmentmaster" />
<entityname="mag_smstext" />
<entityname="annotation" />
<entityname="attachment" />
<entityname="activitymimeattachment" />
<entityname="emailhash" />
<entityname="emailsearch" />
<entityname="metric" />
<entityname="goalrollupquery" />
<entityname="goal" />
<entityname="savedquery" />
<entityname="savedqueryvisualization" />
</entityextractlist>
</wtfetch>