AX1770

Importing from an external database

You can import data into Axiom Software from an external database, such as a SQL Server or Oracle database.

When importing from an external database, you must provide the appropriate information to allow Axiom Software to connect to the database, such as the server and/or database name, and the login credentials. You must also create a SQL statement to query the necessary data that you want to import into Axiom Software.

NOTE: The Connection information is only used to connect to the source and extract the data into the import temp table. If the connection information includes a password, the password must be re-entered anytime the connection information is changed.

Source configuration

To import data from an external database, complete the following configuration settings on the Source tab of the Import Wizard.

Item Description

Import type

Item	Description
Import type	Select External Database to import data directly from an external database.
Import source	Select one of the following database sources: SQL Server: Use this to import from a SQL Server database. Oracle: Use this to import from an Oracle database. OLEDB: Use this to connect to any database that supports OLEDB. ODBC: Use this to connect to any database that supports Open Database Connectivity. Generally speaking, this option should only be used if no other option is available to connect to your desired database. NOTES: The OLEDB option can also be used to import data from files if necessary. However, it cannot be used with Excel files. The Excel file option should be used instead. An ODBC driver is required for use with the ODBC option. See ODBC driver. When using the Oracle option, the Oracle Data Access Connection software (ODAC) must be installed on the Axiom Software application server. If you want to import directly from an Oracle database without installing this software on the application server, you can use the OLEDB import source instead.
Remote Data Connection	If your Axiom Software system is hosted on the Axiom Software cloud service, then you must specify a remote data connection so that the cloud service can connect to the database server located on your network. You can select any remote data connection defined in your system, or you can enter an import variable name. For example, you can enter the built-in system variable `{DefaultRemoteDataConnection}` to automatically use the default remote data connection for your system. For information on how the default remote data connection is determined, see System variables. Remote data connections are defined in Scheduler. For more information, see Managing remote data connections. If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Select External Database to import data directly from an external database.

Import source

Select one of the following database sources:

SQL Server: Use this to import from a SQL Server database.
Oracle: Use this to import from an Oracle database.
OLEDB: Use this to connect to any database that supports OLEDB.
ODBC: Use this to connect to any database that supports Open Database Connectivity. Generally speaking, this option should only be used if no other option is available to connect to your desired database.

NOTES:

The OLEDB option can also be used to import data from files if necessary. However, it cannot be used with Excel files. The Excel file option should be used instead.
An ODBC driver is required for use with the ODBC option. See ODBC driver.
When using the Oracle option, the Oracle Data Access Connection software (ODAC) must be installed on the Axiom Software application server. If you want to import directly from an Oracle database without installing this software on the application server, you can use the OLEDB import source instead.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Software cloud service, then you must specify a remote data connection so that the cloud service can connect to the database server located on your network.

You can select any remote data connection defined in your system, or you can enter an import variable name. For example, you can enter the built-in system variable {DefaultRemoteDataConnection} to automatically use the default remote data connection for your system. For information on how the default remote data connection is determined, see System variables.

Remote data connections are defined in Scheduler. For more information, see Managing remote data connections.

If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Connection information for SQL Server

Complete the following connection information when the Import source is SQL Server.

Item	Description
Server	The name of the SQL Server.
Database	The name of the database.
User	The user name to use to connect to the specified server and database. The user credentials must be for a SQL Server account; network domain credentials cannot be used.
Password	The password to use to connect to the specified server and database.

Once you have completed the connection settings, click the Test connection button to test the connection. The Status updates to show either a success message or an error message.

Connection information for Oracle

Complete the following connection information when the Import source is Oracle.

Field	Description
Server	The connection parameters for the Oracle server. You can obtain this information from the Oracle TNS Names entry. A typical TNS Names entry for Oracle looks like the following: SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID))) Axiom Software requires this information in the following format: MyHOSTNAME:MyPORT/MyOracleServiceID Where: MyHostName is the name of the Oracle server machine MyPort is the port number that the server is listening on, typically 1521 MyOracleServiceID is the name of the Oracle service running on the host machine
User	The user name to use to connect to the database.
Password	The password to use to connect to the database.

Field

Description

Server

The connection parameters for the Oracle server. You can obtain this information from the Oracle TNS Names entry. A typical TNS Names entry for Oracle looks like the following:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHOSTNAME)(PORT=MyPORT))(CONNECT_DATA=(SERVICE_NAME=MyOracleServiceID)))

Axiom Software requires this information in the following format:

MyHOSTNAME:MyPORT/MyOracleServiceID

Where:

MyHostName is the name of the Oracle server machine
MyPort is the port number that the server is listening on, typically 1521
MyOracleServiceID is the name of the Oracle service running on the host machine

User

The user name to use to connect to the database.

Password

The password to use to connect to the database.

Once you have completed the connection settings, click the Test connection button to test the connection. The Status updates to show either a success message or an error message.

Connection information for OLEDB

Complete the Connection string field when the Import source is OLEDB. The connection string identifies the name and location of the database or file to connect to, including any necessary validation information. Any valid SQL connection string can be used. The connection string cannot contain spaces.

Once you have completed the connection settings, click the Test connection button to test the connection. The Status updates to show either a success message or an error message.

A good resource for connection strings is http://www.connectionstrings.com/ (external link). Some examples of common connection strings are shown below:

Source	Sample string
CSV	Server=.\SQLExpress;Provider=MSDASQL;Driver={Microsoft Text Driver (.txt; .csv)};UID=test;PWD=test!123;Database=AxiomFinancial
SQL Server, trusted connection	Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
Oracle with TNS	Data Source=TORCL;User Id=myUsername;Password=myPassword;
SQLOLEDB (standard)	Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
SQLOLEDB (trusted)	Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
SQLOLEDB (server instance)	Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase;Integrated Security=SSPI;
AS400	Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

Connection information for ODBC

Complete the Connection string field when the Import source is ODBC. The connection string identifies the name and location of the database to connect to, including any necessary authentication credentials. The connection string requirements and syntax vary depending on the source database you are attempting to connect to. Consult the documentation from your database vendor to determine an appropriate ODBC connection string for this purpose.

Once you have completed the connection settings, click the Test connection button to test the connection. The Status updates to show either a success message or an error message.

SQL Select Statement

The SQL SELECT statement defines the data query to the source database, resulting in the set of data to be imported to the temp table. You can then perform mapping and transformations on the data before importing into the destination table.

In the SQL Select Statement box, enter any valid SQL statement to define the data query. You can click the SQL editor button to open the Edit SQL dialog. This dialog provides a text editor for entering and reviewing large SQL statements, and several tools to check the statement. For more information, see Creating the SQL SELECT statement.

Variables can be used in the SELECT statement. See Using variables in imports.

NOTE: The syntax of the SQL statement cannot be validated when using OLEDB or ODBC as the source.

ODBC driver

Use of ODBC requires an ODBC driver to be installed on the following servers:

For on-premise systemsAxiom Software systems that are installed on the client's premises. All servers and technical requirements are managed by the client., the driver must be installed on the Axiom Software Application Server.
For cloud service systemsAxiom Software systems that are accessed using the Axiom Cloud Service. Technical requirements are managed by Axiom Support; no server software is installed on the client's premises., the driver must be installed on the local server that is hosting the Axiom Software Cloud Integration Service.

The ODBC driver is specific to your source database. If you want to use ODBC with a particular database, that database vendor should provide or recommend an ODBC driver for use with that database.