Database import

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

You might want to import data from an internal or thrid-party system like BMC Remedy or Service Now. This article guides you through importing data from another database.  Learn more about importing

Supported databases

We're using a common framework (jdbc) to connect to the database, so we support many databases:

Database Type

Driver

Microsoft SQL Server

com.microsoft.sqlserver.jdbc.SQLServerDriver

Oracle

oracle.jdbc.OracleDriver

PostgreSQL

org.postgresql.Driver

MySQL

com.mysql.jdbc.Driver

Import fields

Once you've chosen your import type, you'll need to enter details about it. Here's the description of fields you should see in Insight.

General fields

Here are general fields, common for every import type:

Name

Description

NameThe name of the import.
DescriptionThe description for your convenience.
Concatenator

You can specify a default concatenator. When joining multiple data locators into one Insight attribute, this will be the default concatenator. One example could be to join two columns like "First name" and "Last name" into one attribute. So "Mathias" (first name) and "Edblom" (last name) will be concatenated as "Mathias Edblom" if using \s as concatenator.

Enter \s for space-concatenated. To include a concatenate character, place the value between double quotes (i.e "\s").

Empty ValuesDefines what should happen when a Data Locator is empty. Should the import remove the attribute value or just ignores it and leave the current value as is.
Defines what should happen if a Data Locator is unknown to Insight. This could happen with attribute types like "Status" and "Select". The value can be added as an option or just ignore the value.

Format for date fields in import source to convert dates into Insight. If left empty, Insight will automatically try to find correct format. 
The format should be specified according to the Java SimpleDateFormat guidelines.

Format for date/time fields in import source to convert dates into Insight. If left empty, Insight will automatically try to find correct format. 
The format should be specified according to the Java SimpleDateFormat guidelines.

Module fields

These fields are specific to an import type (module).

Setting

Description

URL

The database connection URL. If you want to connect to the Jira database, you can look into the dbconfig.xml in Jira_HOME to find the correct configuration and URL.

Driver

The driver class to use to connect to the database.

Place the JDBC driver (*.jar) to the <Jira_INSTALL>/lib folder

Don't forget to restart Jira after adding the driver to lib folder.

Username

The username used to authenticate.

Password

The password used to authenticate.

You can enter the password for your database.

If you want to query the Jira database, you can look into the dbconfig.xml found in Jira_HOME directory, to find out the correct configuration.

Scheduling fields

Scheduling fields are responsible for keeping your data in sync:

NameDescription
Synchronizing AccountThe Jira user to use when synchronize data into Insight
Cron ExpressionThe interval for the automatic synchronization.
Automatically SynchronizeIf the import should be scheduled for automatic synchronization.

Pre-defined structure and configuration

Predefined structure and configuration is not available for this import type.

Import configuration created

You can now view your import configuration, but it's not ready yet. You still need to create or review the object type and attribute mapping, and make sure there are no problems with your import configuration.

When you're ready, go to 2. Create object type and attribute mapping.

Before you go

In the next step, you'll create the object type and attribute mappings. Here are some settings specific to the database import type.

Object type mapping configuration

Name

Description

Selector

The selector for the Database import is a SQL query. How to build SQL queries depends on the database type.

How to build up the selector (the SQL statement) depends on how you have configured the database connection. Below you will see some examples that will hopefully guide you in the right direction when setting this up.

Database Type

Example

Microsoft SQL Server

select * from <database.name>.<schema-name>.Application

<schema-name> is "dbo" as default so an example could be:

select * from dbname.dbo.Application
Oracle

select * from <schema-name>.Application

If <schema-name> is "Jiraschema" the query will be:

select * from Jiraschema.Application



PostgreSQL
select * from "Application"
MySQL

select * from Application

If you want to query the Jira database, you can look into the dbconfig.xml found in Jira_HOME directory, to find out the correct configuration, such as schema name etc.

Last modified on Apr 27, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.