Load JIRA data into SQL Server with ODBC – Call REST API

Generate API Token

To access JIRA data using REST API call you need to generate an API Token in your Atlassian account. Follow the steps mentioned below to generate API Token:

1. you can directly login to the JIRA Atlassian Cloud site with your credentials, and redirect to the JIRA Atlassian Cloud Account's Profile Page for generating an API Token.
2. Click API tokens, then Create API token.
   

   Generate API Token - Step - 1

3. Create API Token - Give Label name to your API token which you are generating now
   

   Generate API Token - Step - 2

4. Use Copy to the clipboard, and paste the token into your or elsewhere:

   Note:  * For security reasons it isn't possible to view the token after closing the creation dialog; if necessary, create a new token. * You should store the token securely, just as for any password.

   

   Generate API Token - Step - 3

5. If you forgot API token, then you can Revoke your old API token and create new API token OR you can create multiple API tokens too.
   

   Generate API token - Step - 4

Now we have successfully generated an API token. Let´s start with an example. In this example, we will show how to read Tasks/Issues data from JIRA and store that data into a SQL table with SSIS.

Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.

1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and  Get SSMS
2. Open SSMS and connect to SQL Server.
3. Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...
   

   Add Linked Server in SQL Server

4.  Now enter the linked server name, select Provider as SQL Native Client
5. Enter data source as GatewayServerName,PORT_NUMBER where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab in case its different.
6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column
   

   Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

7. Click on Security Tab and select last option "Be made using this security context". Enter your gateway user account here.

8. Optional: Under the Server Options Tab, Enable RPC and RPC Out and Disable Promotion of Distributed Transactions(MSDTC).

   

   RPC and MSDTC Settings

   ---

   You need to enable RPC Out if you plan to use EXEC(...) AT [MY_LINKED_SERVER_NAME] rather than OPENQUERY.
   If don't enabled it, you will encounter the 'Server "MY_LINKED_SERVER_NAME" is not configured for RPC' error.

   Query Example: EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]

   ---

   If you plan to use 'INSERT INTO...EXEC(....) AT [MY_LINKED_SERVER_NAME]' in that case you need to Disable Promotion of Distributed Transactions(MSDTC).
   If don't disabled it, you will encounter the 'The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER_NAME" was unable to begin a distributed transaction.' error.

   Query Example:

   Insert Into dbo.Products 
   EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]

   ---

9. Click OK to save Linked Server
10. In SSMS execute below SQL query to test your connectivity.

    SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME, 'SELECT * FROM $')

11. Here is the preview after you run some REST API query in SQL Server. Notice that you can override default configuration by supplying many parameters in WITH clause (second query example in the screenshot).
    ODBC - Query API Data in SQL Server

    SSMS Output - Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)

12. You can wrap your queries inside View or wrap inside Stored procedure to parameterize. Here is an example of creating the view which calls REST API queries. Below View can be consumed like a normal table from any Tools or Programming Language which supports connectivity to SQL Server.

    CREATE VIEW dbo.vwApiInvoices 
    AS 
    /*Call REST API inside SQL Server View*/
    SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME , 'SELECT * FROM $');
    
    GO
    

13. Notice in above approach if you parameterize Stored Procedure then check this article to understand Dynamic Metadata.
14. Now let's insert JIRA data into the new data table "tblissues"  in the SQL server database. For that, we need to execute below SQL query.

    SELECT * into tblissues FROM OPENQUERY( MY_LINKED_SERVER_NAME, 'SELECT * FROM $')

    Insert Data into the table
15. Let's insert and update records into the already created table "tblissues" .
    • To do that first we need to insert the new JIRA data into the "#temp_tblissues" temporary database table.
    • Now let's delete the old records from the database table which are updated.
    • At the last insert all new JIRA data into the database table.
    • To do that, we need to query like below SQL queries.

    SELECT * into #temp_tblissues FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')
    
    DELETE FROM [dbo].[tblissues] WHERE id in (SELECT id FROM #temp_tblissues)
    
    INSERT INTO tblissues
    SELECT * FROM #temp_tblissues

     
16. In the upper step, we see how to insert and update new records. Now if you want to update particular fields records only then you need to query like this. In this query, We are updating "fields_duedate"  and "fields_issuetype_name"  fields only.

    Update dbti_1
    set dbti_1.fields_duedate = jira_2.fields_duedate,
    dbti_1.fields_issuetype_name = jira_2.fields_issuetype_name
    FROM tblissues dbti_1
    JOIN (SELECT * FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')) jira_2 on dbti_1.id=jira_2.id

    * If you are getting error like : "Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation" then you need to query like this :

    Update dbti_1
    set dbti_1.fields_duedate = jira_2.fields_duedate,
    dbti_1.fields_issuetype_name = jira_2.fields_issuetype_name
    FROM tblissues dbti_1
    JOIN (SELECT top 1 * FROM OPENQUERY(MY_LINKED_SERVER_NAME, 'SELECT * FROM $')) jira_2 
    on dbti_1.id=jira_2.id 
    COLLATE SQL_Latin1_General_CP1_CI_AS