Home >

AX2434

Import source: OLEDB

Use the OLEDB option on the Import Wizard's Source tab to connect to any database or file that supports OLEDB, with the following exceptions:

• If the database is a SQL Server or Oracle database, you can use the database-specific options instead. However, you can use OLEDB to connect to these database types if desired.
• If the file is an Excel file, OLEDB cannot be used. Use the Excel File option instead.

The Source tab of the Import Wizard uses the following settings when importing data using an OLEDB connection.

Item	Description
Source	Select OLEDB to read data directly from a database or a file using an OLEDB connection.
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

The connection string identifies the name and location of the database or file to connect to, including any necessary validation information. In the Connection string box, enter the connection string to the source database or file. Any valid SQL connection string can be used. The connection string cannot contain spaces.

NOTE: This information is only used to connect to the source and extract the data into the import temptable. Transformation steps are not performed using these credentials.

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.

NOTE: If the connection string contains a password, that password must be re-entered whenever any of the other connection properties are changed.

A good resource for connection strings is http://www.connectionstrings.com/. 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 2005, 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;

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 temptable. 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 as the source.