Create and configure a database data source

Use a database data source (SQL & ODBC) to bring data from a database into Data Manager.

This task requires Data Manager Adapters admin permissions.

Create a database data source

1. From the app switcher, select Assets.

2. Select Data Manager in the sidebar.

3. Select Object classes.

4. Select the Show data sources button beside the desired object class

5. Select + Add.

6. [ 一般] で、[ SQL & ODBC] を選択します。

7. [次へ] をクリックします。

8. Complete all required (*) fields, before clicking Configure your data source.

Configure a database data source

In the SQL & ODBC configuration screen:

• Data source name – the name of the data source used to run, transform, map, cleanse, and import the data.

• Enabled – leave this toggle enabled. Disabled jobs cannot be run.

• Visible – enable this if the data source is to be visible in the Object classes view.

• Short display name – a unique name as a data source label.

• Data source type – fixed to Schema for this data source. You must create your data source type for use, prior to configuring your data source.

• Connection type – choose between Microsoft SQL Server Native and Open Database Connectivity (ODBC).

• Complete all required (*) fields before clicking Configure your data source.

Any secure values (passwords, secrets) are encrypted and shown as ********************. You can delete and re‑enter them when editing a job.

Microsoft SQL Server Native ソースを設定する

If you select Microsoft SQL Server Native:

• Click Generate Connection String; which will show

  • Database address – address or hostname of the database server.

  • Database name – name of the database.

  • Integrated security – choose:

    • On – use Windows integrated security.

    • Off – use explicit username and password.

      • Where integrated security is off:

        • Database username – must have read‑only access.

        • Database password – password for the database username.

• Click Generate connection string against, to generate and verify the connection string.

• Query – SQL query used to retrieve records.

The database user must have read‑only access to the database.

Configure an ODBC source

If you select Open Database Connectivity (ODBC):

1. Open the ODBC Data Source Administrator (32‑bit or 64‑bit depending on your client).

2. Configure ODBC using one of two approaches (ensure the correct driver is installed):

• User DSN – create a User DSN:

  • Usage example: dsn=UserDataSourceName;UID=UserId;PWD=Password;

• Connection string – provide a full connection string, for example:

  • MSSQL: Driver="{SQL Server}";Server=ServerAddress;Database=DatabaseName;UID=User;PWD=Password;

  • MS Access: Driver="{Microsoft Access Driver (*.mdb)};DBQ=C:\...mdb;UID=User;PWD=Password;

  • Oracle: Driver="{Microsoft ODBC for Oracle}";Server=OracleServer;UID=UserId;PWD=Password;

  • Excel (.xls): provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\...xls';Extended Properties='Excel 8.0;HDR=No;IMEX=1;MAXSCANROWS=0;'

  • Excel (.xlsx): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\...xls;Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;MAXSCANROWS=0;'