How to connect Jira to Talend Studio

Introduction

You can connect to your Jira data in Talend Studio using the high-performance Jira ODBC Driver (powered by Jira Connector). We'll walk you through the entire setup.

Let's not waste time and get started!

Create data source in ZappySys Data Gateway

In this section we will create a data source for Jira in the Data Gateway. Let's follow these steps to accomplish that:

1. Download and install ODBC PowerPack (if you haven't already).

2. Search for gateway in the Windows Start Menu and open ZappySys Data Gateway Configuration:

   

3. Go to the Users tab and follow these steps to add a Data Gateway user:

   • Click the Add button
   • In the Login field enter a username, e.g., john
   • Then enter a Password
   • Check the Is Administrator checkbox
   • Click OK to save
   

4. Now we are ready to add a data source:

   • Click the Add button
   • Give the Data source a name (have it handy for later)
   • Then select Native - ZappySys API Driver
   • Finally, click OK
   JiraDSN

   ZappySys API Driver

   

5. When the Configuration window appears give your data source a name if you haven't done that already, then select "Jira" from the list of Popular Connectors. If "Jira" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

   JiraDSN

   Jira

   

6. Select your authentication scenario below to expand connection configuration steps to:

   • Configure the authentication in Jira.
   • Enter those details into the ZappySys API Driver data source configuration.
   API Key based Authentication

   Jira authentication

   Firstly, login into your Atlassian account and then go to your Jira profile:

   1. Go to Profile > Security.
   2. Click Create and manage API tokens.
   3. Then click Create API token button and give your token a label.
   4. When window appears with new API token, copy and use it in this connection manager.
   5. That's it!

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to API Key based Authentication [Http]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Finally, hit OK button:

   JiraDSN

   Jira

   API Key based Authentication [Http]

   https://[$Subdomain$].atlassian.net/rest/api/3

   Required Parameters
   Subdomain	Fill-in the parameter...
   Atlassian User Name (email)	Fill-in the parameter...
   API Key	Fill-in the parameter...
   Optional Parameters
   CustomColumnsRegex
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

   Find full details in the Jira Connector authentication reference.

   Personal Access Token (PAT) Authentication

   Jira authentication

   Follow official Atlassian instructions on how to create a PAT (Personal Access Token) for JIRA

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to Personal Access Token (PAT) Authentication [Http]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Finally, hit OK button:

   JiraDSN

   Jira

   Personal Access Token (PAT) Authentication [Http]

   https://[$Subdomain$].atlassian.net/rest/api/3

   Required Parameters
   Subdomain	Fill-in the parameter...
   Token (PAT Bearer Token)	Fill-in the parameter...
   Optional Parameters
   CustomColumnsRegex
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

   Find full details in the Jira Connector authentication reference.

   OAuth (**Must change API Base URL to V3 OAuth**)

   Jira authentication

   OAuth App must be created in Atlassian Developer Console. It is found at https://developer.atlassian.com/console/myapps/ [API reference]

   Firstly, login into your Atlassian account and then create Jira application:

   1. Go to Atlassian Developer area.

   2. Click Create and select OAuth 2.0 integration item to create an OAuth app:

      

   3. Give your app a name, accept the terms and hit Create:

      

   4. To enable permissions/scopes for your application, click Permissions tab, then hit Add button, and click Configure button, once it appears:

      

   5. Continue by hitting Edit Scopes button to assign scopes for the application:

      

   6. Select these scopes or all of them:

      

   7. Then click Authorization option on the left and click Add button:

      

   8. Enter your own Callback URL (Redirect URL) or simply enter https://zappysys.com/oauth, if you don't have one:

      

   9. Then hit Settings option and copy Client ID and Secret into your favorite text editor (we will need them in the next step):

      

   10. Now go to SSIS package or ODBC data source and in OAuth authentication set these parameters:

       • For ClientId parameter use Client ID value from the previous steps.
       • For ClientSecret parameter use Secret value from the previous steps.
       • For Scope parameter use the Scopes you set previously (specify them all here):
         • offline_access (a must)
         • read:jira-user
         • read:jira-work
         • write:jira-work
         • manage:jira-project
         • manage:jira-configuration
         NOTE: A full list of available scopes is available in Atlassian documentation.
       • For Subdomain parameter use your Atlassian subdomain value (e.g. mycompany, if full host name is mycompany.atlassian.net).
   11. Click Generate Token to generate tokens.
   12. Finally, select Organization Id from the drop down.
   13. That's it! You can now use Jira Connector!

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to OAuth (**Must change API Base URL to V3 OAuth**) [OAuth]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Press Generate Token button to generate the tokens.
   5. Finally, hit OK button:

   JiraDSN

   Jira

   OAuth (**Must change API Base URL to V3 OAuth**) [OAuth]

   https://[$Subdomain$].atlassian.net/rest/api/3

   Required Parameters
   ClientId	Fill-in the parameter...
   ClientSecret	Fill-in the parameter...
   Scope	Fill-in the parameter...
   ReturnUrl	Fill-in the parameter...
   Organization Id (Select after clicking [Generate Token])	Fill-in the parameter...
   Optional Parameters
   Custom Columns for output (Select after clicking [Generate Token])
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

   Find full details in the Jira Connector authentication reference.

7. Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

   

   ZappySys API Driver - Jira

   Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

   JiraDSN

   

8. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in Talend Studio to retrieve data from Jira. Hit OK button to use this query in the next step.

   SELECT * FROM Issues
   
   --//Query single issue by numeric Issue Id
   --SELECT * FROM Issues Where Id=101234
   
   --//Query issue by numeric Issue Ids (multiple)
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='101234,101235,101236')
   
   --//Query issue by Issue Key(s) (alpha-numeric)
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11')
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11,PROJ-12,PROJ-13')
   
   --//Query issue by project(s)
   --SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ')
   --SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ,KAN,CS')
   
   --//Query issue by JQL expression
   --SELECT * FROM Issues WITH(SearchBy='Jql', Jql='status IN (Done, Closed) AND created > -5d' )

   
   Some parameters configured in this window will be passed to the Jira API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

9. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in Talend Studio:

   

   ZappySys API Driver - Jira

   Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

   JiraDSN

   SELECT * FROM Issues
   
   --//Query single issue by numeric Issue Id
   --SELECT * FROM Issues Where Id=101234
   
   --//Query issue by numeric Issue Ids (multiple)
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='101234,101235,101236')
   
   --//Query issue by Issue Key(s) (alpha-numeric)
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11')
   --SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11,PROJ-12,PROJ-13')
   
   --//Query issue by project(s)
   --SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ')
   --SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ,KAN,CS')
   
   --//Query issue by JQL expression
   --SELECT * FROM Issues WITH(SearchBy='Jql', Jql='status IN (Done, Closed) AND created > -5d' )

   
   You can also access data quickly from the tables dropdown by selecting <Select table>.
   A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the Jira API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Jira servers).

10. Click OK to finish creating the data source.

11. Once done, go to the Network Settings tab and Add a firewall rule for inbound traffic:

    
    • This will initially allow all inbound traffic.
    • Click Edit IP filters to restrict access to specific IP addresses or ranges.

12. Crucial Step: After creating or modifying the data source, you must:

    • Click the Save button to persist your changes.
    • Hit Yes when prompted to restart the Data Gateway service.

    This ensures all changes are properly applied:

    
    Skipping this step may cause the new settings to fail, preventing you from connecting to the data source.

Read Jira data in Talend Studio

To read Jira data in Talend Studio, we'll need to complete several steps. Let's get through them all right away!

Create connection for input

1. First of all, open Talend Studio
2. Create a new connection:
3. Select Microsoft SQL Server connection:
4. Name your connection:
5. Fill-in connection parameters and then click Test connection:
   JiraDSN

   
6. If the List of modules not installed for this operation window shows up, then download and install all of them:
   Review and accept all additional module license agreements during the process
7. Finally, you should see a successful connection test result at the end:

Add input

1. Once we have a connection to ZappySys Data Gateway created, we can proceed by creating a job:

   

2. Simply drag and drop ZappySys Data Gateway connection onto the job:

   

3. Then create an input based on ZappySys Data Gateway connection:

   

4. Continue by configuring a SQL query and click Guess schema button:

   

5. Finish by configuring the schema, for example:

   

Add output

Run the job

Jira Connector actions

Got a specific use case in mind? We've mapped out exactly how to perform a variety of essential Jira operations directly in Talend Studio, so you don't have to figure out the setup from scratch. Check out the step-by-step guides below:

• Read Resources
• Read Fields
• Read Custom Fields
• Read Issue Types
• Read Application Roles
• Read Groups
• Read Users
• Create User
• Delete User
• Read Projects
• Create Project
• Upsert Project
• Delete Project
• Read Issues
• Read Issue (By Id)
• Create Issues
• Update Issue
• Delete Issue
• Read Worklogs
• Read Worklogs modified after a specified date
• Create Worklog
• Update Worklog
• Delete Worklog
• Read Comments
• Create Issue Comment
• Update Issue Comment
• Delete Issue Comment
• Read Changelogs
• Read Changelog Details
• Read Changelogs by IDs
• Get custom field contexts
• Get custom field context options
• Make Generic REST API Request
• Make Generic REST API Request (Bulk Write)