SQL Organisation Import

From Hornbill
Revision as of 16:50, 2 October 2019 by Victors (talk | contribs)
Jump to navigation Jump to search

About the SQL Organisation Import Utility

The utility provides a quick and easy method of uploading and updating organisations from an external data source into Hornbill instance.

Open Source

The Hornbill SQL Organisation Import Utility is provided open source under the Hornbill Community Licence and can be found Here on GitHub

Installation Overview

Windows Installation

  • Download the ZIP archive relevant to your OS and architecture. This contains the tool executable file, configuration file and license;
  • Extract the ZIP archive into a folder you would like the application to run from e.g. 'C:\organisation_import\'.

Configuration Overview

The configuration of this utility is managed through a JSON file (conf.json), which is supplied with each release: 

   "APIKey": "your_api_key_here",
   "InstanceId": "your_instance_id_here",
   "OrganisationAction": "Create/Update/Both",
   "SQLConf": {
       "Driver": "",
       "Server": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "Port": 0,
       "Encrypt": false,
       "OrganizationName": "FIELD 1",
       "Query": "SELECT <field_list> FROM <table_or_file>"
   },
   "OrganizationMapping":{
       "organization_name":"FIELD 1",
       "address":"FIELD 2",
       "city":"FIELD 3",
       "state":"FIELD 4",
       "postal_code":"FIELD 5",
       "country":"FIELD 6",
       "industry":"FIELD 7}",
       "phone_number":"FIELD 8",
       "website":"FIELD 9",
       "language":"FIELD 10",
       "custom_1":"FIELD 11",
       "custom_2":"FIELD 12",
       "custom_3":"FIELD 13",
       "custom_4":"FIELD 14",
       "custom_5":"FIELD 15",
       "custom_6":"FIELD 16",
       "custom_7":"FIELD 17",
       "custom_8":"FIELD 18",
       "custom_9":"FIELD 19",
       "custom_10":"FIELD 20",
       "custom_11":"FIELD 21"
   }


  • APIKey - a Hornbill API key for a user account with the correct permissions to carry out all of the required API calls
  • InstanceId - Hornbil instance ID. You can get the instance ID from your URL used to access Hornbill: https://live.hornbill.com/your_instance_id/
  • OrganisationAction - one of the following values:
    • Create = Import will only create new organisations in your instance.
      • If the imported organisation already exists and this is set to "Create" then the organisation will not be created
    • Update = Import will only update existing organisations in your instance.
      • If the imported organisation does not exist and this is set to "Update" then the organisation will not be updated
    • Both = Import will create new organisations and update existing organisations in your instance.
      • If the imported organisation does not exist and this is set to "Both" then the organisation will be created
      • If the imported organisation does exist and this is set to "Both" then the organisation will be updated


SQLConf

  • Driver - the driver to use to connect to the database that holds the asset information. Needs to be one of the following:
    • mssql = Microsoft SQL Server (2005 or above)
    • mysql = MySQL Server 4.1+, MariaDB
    • mysql320 = MySQL Server v3.2.0 to v4.0
    • csv = ODBC Data Source using MS Access Text Driver (*.txt, *.csv)
    • excel = ODBC Data Source using MS Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)

NOTE: To create a 64 bit ODBC connector using MS Access Text Driver (for CSV files) you would need the Microsoft Access Database Engine which can be found as an MS 2010 Redistributable or as an optional part of the Microsoft Office Suite.


When using mssql, mysql or mysql320 drivers, the Server, Database, UserName, Password and Port parameters should be populated accordingly:

  • Server = the address of the SQL server (e.g. localhost)
  • Database = the name of the Database to connect to
  • UserName = this should be the SQL authentication Username to connect to the Database (if any, e.g. root)
  • Password = this should be the password for the above username (if any)
  • Port = the SQL port (e.g. 5002)


When using csv or excel as a data source, the Database parameter should be populated accordingly:

  • Database = this should be populated with the name of the ODBC connection (the name of the Data Source - DSN) on the PC that is running the tool


In addition, when using any of the available drivers, the Encrypt, OrganizationName and Query parameters should also be populated accordingly:

  • Query = this should be the SQL query to retrieve the organisation records (e.g. SELECT * FROM <datbase_table_or_file>)
  • OrganizationName = specify which field from your data source contains the organisation name
  • Encrypt = boolean value to specify whether the connection between the script and the database should be encrypted.

NOTE: There is a bug in SQL Server 2008 and below that causes the connection to fail if the connection is encrypted. Only set this to true if your SQL Server has been patched accordingly.


OrganizationMapping

  • Maps data from your data source into the generic Hornbill organisation record
  • Any value wrapped with "" will be populated with the corresponding response from the SQL Query and is treated literally as a written example.

Execute

Command Line Parameters:

  • file - this should point to your JSON configuration file and by default looks for a file in the current working directory called conf.json. If this is present you don't need to have the parameter.
  • dryrun - if set to True the XMLMC for Create and Update organisations will not be called and instead the XML will be dumped to the log file, so you can ensure the data mappings are correct before running the import. Defaults to `false`.

'goOrganisationImport_x64.exe -file=conf.json'

Preparing to run the tool

  • Open conf.json and add in the necessary configuration;
  • Open Command Line Prompt as Administrator;
  • Change directory to the folder with goOrganisationImport_x64 *.* executables 'C:\organisation_import
    • On 32 bit Windows PCs: goOrganisationImport_x86.exe
    • On 64 bit Windows PCs: goOrganisationImport_x64.exe
  • Follow all on-screen prompts, taking careful note of all prompts and messages provided.

Logging Overview

All Logging output is saved in the log directory in the same directory as the executable the file name contains the date and time the import was run 'SQL_Organization_Import_YYYYMMDDHHMMSS.log'

Trouble Shooting

Common Error Messages

Below are some common errors that you may encounter in the log file and what they mean:

  • [ERROR] Error Decoding Configuration File:..... - this will be typically due to a missing quote (") or comma (,) somewhere in the configuration file. This is where an online JSON viewer/validator can come in handy rather than trawling the conf file looking for that proverbial needle in a haystack.

Error Codes

  • 100 - Unable to create log File
  • 101 - Unable to create log folder
  • 102 - Unable to Load Configuration File

HTTP Proxies

If you use a proxy for all of your internet traffic, the HTTP_PROXY Environment variable needs to be set. The https_proxy environment variable holds the hostname or IP address of your proxy server. It is a standard environment variable and like any such variable, the specific steps you use to set it depends on your operating system.

For windows machines, it can be set from the command line using the following:
set HTTP_PROXY=HOST:PORT
Where "HOST" is the IP address or hostname of your Proxy Server and "PORT" is the specific port number.

Scheduling Overview

Windows

You can schedule goHornbillDataExport.exe to run with any optional command line argument from Windows Task Scheduler.

  • Ensure the user account running the task has rights to goHornbillDataExport.exe and the containing folder.
  • Make sure the Start In parameter contains the folder where goHornbillDataExport.exe resides in otherwise it will not be able to pick up the correct path.

Change Log

v1.0.0 - 02/10/2019

Initial Release

Retrieved from "https://wiki.hornbill.com/index.php?title=SQL_Organisation_Import&oldid=21404"