DB Query - Data360_Analyze - Latest

Connect to a MS Access or SQL Server database

The DB Query node supports a 64-bit ODBC connection to a SQL Server and MS Access database. To setup the ODBC link, you must first configure an ODBC DSN:

1. Launch the OBDC Data Source Administrator.
2. Select either the User DSN or the System DSN tab (A User DSN is only accessible by the user who created it, whereas a System DSN is available to all users).
3. Click Add.

   The Create New Data Source dialog opens.

4. If you are connecting to a MS Access database, from the list of drivers, select Microsoft Access Driver.

   Enter a Data Source Name and click Select.

   If you are connecting to a SQL Server database, from the list of drivers, select SQL Server. Then, enter a Name for the data source and select or type the server name and SQL Server instance in the following format: ServerName\InstanceName

   Click Next.

   The following additional steps are only for connecting to an SQL Server database:

5. Select the authentication mode to log into the SQL Server. Then, if you selected SQL Server authentication, enter the Login ID and Password and click Next.
6. Select Change the default database to and select the database from the list. If you do not see the database that you want to connect to in the list, ensure that the user that you entered in the previous step has rights to the database
7. Click Next.
8. Click Finish.

   When the summary dialog opens, click Test Data Source to ensure that the DSN is configured correctly.

Connect to an Oracle database

To connect to an Oracle database, you will need client software. This example uses Oracle's Instant Client:

1. Download Oracle Instant Client from Oracle's website.
2. Extract the files and place them in a directory on your hard drive.
3. Create a .txt file in the directory where you extracted the Instant Client files, and name the file tnsnames.ora.

4. Type the connection information for your database (Identifier, host and SID) in your tnsnames.ora .txt file, as per this example:

   IDENTIFIER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=SOMEHOST)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=ORA10)))

5. Set the path variable to include the location of the tnsnames.ora file:From the Control Panel, open System and Security > System > Advanced system settings.

   The System Properties dialog opens.

6. Select the Advanced tab and click Environment Variables.
7. To change the path variable for all users, in the System variables section, highlight Path and click Edit.... To change the path variable for the current user only, in the User variables... section, highlight Path and click Edit....

   The Edit System Variable dialog opens.

8. Type the path to the Instant Client files in the Variable value field and click OK.
9. From the Environment Variables dialog, click New...

   The New System Variable dialog opens.

10. In the Variable name field, type TNS_ADMIN, then in the Variable value field, type the path to the Instant Client directory.
11. Click OK.

Properties

SqlQuery

Specify the query to extract the data. For example:

select * from CustomerInfo

A value is required for this property.

DbUser

Specify the database username to connect to the database.

A value is required for this property.

DbPassword

Specify the database user's password to connect to the database.

A value is required for this property.

DbService

Specify the database instance name, for example:

CustomerInfo

A value is required for this property.

DbInterface

Optionally specify the type of interface. Enter one of the following (bold text only):

• oci - Oracle
• cli - Teradata
• odbc - MS Access and SQL Server

The default value is oci.

PostProcessScript

Optionally specify Script to modify data after extraction from the database.

SqlDateType

Optionally specify a date type to map to an SQL DATE.

Choose from:

• date
• datetime

Inputs and outputs

Inputs: None.

Outputs: out1.