Create and configure a database job

Create a database job

To create a new job using a database file as a data source:

1. Select Assets in the top navigation bar.

2. Under the Asset and configuration management title, select Data Manager.

3. Select Adapters in the left menu bar. This will open a new tab to the Adapters screen.

4. Select Create a new job in the left menu bar.

5. Under General, select SQL.

Once you have created a new job, you must complete all of the Required (*) fields before the job can be successfully saved.

Configure a database job

In the SQL Configuration screen, configure details about the data source:

1. In the Name field, enter a name for your job. This name will be used to identify your job when you run the Adapters client.

2. Ensure the Enabled toggle is set to Enabled (dark blue). Disabled jobs cannot be run.

3. In the Object class field, select the Object Class destination for this data.

4. In the Data Source Type field, select an existing data source or create a new data source type, below.

5. In the Data Source Name field, enter a name for the Data Source that will be created when this job is run. This is typically the same as the Name, above.

6. In the Connection Type field, select between Microsoft SQL Server Native and Open Database Connectivity (ODBC).

Create a new data source type when creating a new job

A data source type contains information about a group of data sources, and you are required to select a data source type when creating a job. If you don’t have any data source types created, you can create one from within the SQL Configuration screen by typing in the Data Source field.

Configure a Microsoft SQL Server Native source

If you’ve selected a Microsoft SQL Server Native source, configure further details about your database:

1. In the Database Address field, enter the address of your database.

2. In the Database Name field, enter the name of the database.

3. Use the Integrated Security toggle to select between on (integrated security) and off (non-integrated security).

4. If you’ve set the Integrated Security toggle, above, to Off, then you will need to enter a Database Username with connection rights to the database.

5. If you’ve set the Integrated Security toggle, above, to Off, then you will need to enter a Database Password with connection rights to the database.

1. Select Generate Connection String.

2. Enter a Query to retrieve records from the database.

Configure an Open Database Connectivity (ODBC) source

If you’ve selected a Open Database Connectivity (ODBC) source, configure further details about your database:

1. Open the ODBC Datasource (If you are using the ATDA 64bit, open the ODBC 64bit version otherwise open the ODBC 32bit version).

2. Configure the Open Database Connectivity (ODBC) source. There are 2 ways to use the ODBC connection. Make sure that the driver has been installed for whichever option you choose:

   1. Add a User DSN

      Add a User DSN and select the driver to create a new User Data Source Name 'DSN'. Usage:

      1 2 3 4 dsn=UserDataSourceName; dsn=UserDataSourceName; UID=UserId; PWD=Password;

   2. Write a connection string

      Write a connection string. Example:

      1 2 3 4 5 MSSQL: Driver="{SQL Server}";Server=ServerAddress;Database=DatabaseName;UID=User;PWD=Password; MSAccess: Driver="{Microsoft Access Driver (*.mdb)}; DBQ=C:\...mdb;UID=User;PWD=Password; Oracle: Driver="{Microsoft ODBC for Oracle};Server=OracleServer; UID=UserId;PWD=Password; MSExcel (.xls): provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\...xls';Extended Properties='Excel 8.0;HDR=No;IMEX=1;MAXSCANROWS=0;' MSExcel (.xlsx): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\...xls;Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;MAXSCANROWS=0;'`);

Save a job

Once all required fields have been filled and all columns in the data source have been mapped, you can Save this job. Your new job should be visible in the All Jobs screen.