{"id":5946,"date":"2019-01-09T08:44:01","date_gmt":"2019-01-09T08:44:01","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5946"},"modified":"2025-03-19T07:07:08","modified_gmt":"2025-03-19T07:07:08","slug":"insert-update-delete-jira-issue-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/insert-update-delete-jira-issue-sql-server\/","title":{"rendered":"Insert, Update and Delete JIRA Issue in SQL Server"},"content":{"rendered":"

Introduction<\/span><\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new API Connector for Jira Online<\/a> which makes it much simpler to\u00a0Read\/Write Jira Data in ODBC<\/strong> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in SSIS API Source<\/a> \/ SSIS API Destination<\/a> OR\u00a0API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<\/p>\n<\/div><\/div>\n\"Write<\/a>In our previous blog we saw how to read JIRA data in SQL Server<\/a>. In this blog, we will learn how to\u00a0Insert, Update and Delete Issue in JIRA using ZappySys JSON Driver. <\/strong>We will also read data from SQL Server data table in stored-procedure and insert JIRA issues in bulk, along with few other topics such as how to generate an API Token using Atlassian Account for\u00a0JIRA REST API Call<\/strong>, how to update and delete Issue data in JIRA using REST API calls.<\/p>\n

So, let\u2019s get started.<\/p>\n

<\/h2>\n

Requirements<\/span><\/h2>\n
    \n
  1. A first requirement will be to SQL Server Database Engine Installed<\/li>\n
  2. The second requirement will be SSMS installed<\/li>\n
  3. Finally, make sure to have\u00a0ZappySys ODBC PowerPack<\/a>\u00a0installed.<\/li>\n<\/ol>\n

    What is JIRA<\/h2>\n

    JIRA is Atlassian\u2019s popular project- and issue-tracking platform used by development and technical support teams to get more work done and faster.<\/p>\n

    It combines:<\/p>\n

    issue tracking<\/strong> – a software application that allows to record and follow the progress of every problem or “issue” that user identifies until the problem is resolved.
    \nagile project management<\/strong> – an iterative approach to planning and guiding project processes. customizable workflow, and a pluggable integration – integrates with Freshdesk, GitHub, Zendesk, Zapbook, Asana and so on.<\/p>\n

    Getting Started<\/h2>\n

    First of all, we will see several examples. ZappySys includes an\u00a0ODBC JSON Source Connector<\/a>\u00a0that will help you to call JIRA API, Insert issue in JIRA using static data from the SQL server using ODBC JSON driver REST API Call. Update and Delete JIRA issue data From the SQL Server. Insert Bulk JIRA issues from the stored procedure. To learn more about JIRA API check this help file<\/a>.<\/p>\n

    Generate API Token<\/h2>\r\nTo access JIRA data using REST API call you need to generate an API Token in your Atlassian account.\u00a0Follow the steps mentioned below to generate API Token:\r\n
      \r\n \t
    1. you can directly login to the JIRA Atlassian Cloud site<\/a> with your credentials, and redirect to the JIRA Atlassian Cloud Account's\u00a0Profile Page<\/a> for generating an API Token.<\/li>\r\n \t
    2. Click\u00a0API tokens<\/strong>, then\u00a0Create API token<\/strong>.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 1<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    3. Create API Token - Give Label name to your API token which you are generating now\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 2<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    4. Use\u00a0Copy to the clipboard<\/strong>,\u00a0and paste the token into your or elsewhere:\r\n
      Note:\u00a0 * 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.<\/blockquote><\/div>\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API Token - Step - 3<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    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.\r\n
      \r\n\r\n\"Generate<\/a>\r\n

      Generate API token - Step - 4<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\nNow we have successfully generated an API token. Let\u00b4s 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.<\/div>\n

      About JIRA REST API<\/h2>\n

      JIRA On-premises and Cloud Version both offers REST API. Here are some examples of\u00a0JIRA REST API.\u00a0For inserting issue in JIRA we will use JIRA REST API create issue method<\/a>. We can also insert issues in bulk using Create issues bulk method<\/a>. To update issue we will use this PUT method:\u00a0Updating an Issue via the JIRA REST APIs<\/a>. To delete issue we will use this DELETE method<\/a>. You can find JIRA Rest API example on this link: Jira REST API examples<\/a><\/p>\n

      Step-by-Step: Write in JIRA from SQL Server<\/span><\/h2>\n

      Now once you have API token in the JIRA Atlassian Cloud website we can move to ODBC JSON Driver Configuration Part. For this example purpose you can use the following URL:<\/p>\n

      https:\/\/YOUR-DOMAIN.atlassian.net\/rest\/api\/2\/issue\/<\/pre>\n
      hence,\u00a0your-domain is your Atlassian Cloud account\u2019s domain name (we are using zappysys2.atlassian.net).<\/strong><\/p>\n
      Above all, each API endpoint may have a different limit for the number of items returned, and these limits may change without notice. (For more See this\u00a0JIRA REST API reference<\/a>)<\/p>\n
      Configure ZappySys Gateway data Source for JIRA<\/span><\/h3>\n
        \n
      1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:\n
      2. Add the Native – ZappySys JSON Driver Data source.\n
      3. Now it’s time to connect with JIRA. Let’s use JSON Driver Data source with the\u00a0following URL:\n
        https:\/\/YOUR-DOMAIN.atlassian.net\/rest\/api\/2\/issue\/<\/pre>\n
        \"ODBC<\/a>
        ODBC JSON Driver: Enter Request URL<\/p><\/div><\/li>\n
      4. It is time to configure the Other Settings. Select HTTP Connection Type from the Connection Type drop-down and Click on Click to Configure Link to Configure the Connection Manager.\n
      5. Now, Let’s Add request body input data of issue.\n
        {\r\n    \"fields\": {\r\n       \"project\":\r\n       {\r\n          \"id\": \"<YOUR-PROJECT-ID>\"\r\n       },\r\n       \"summary\": \"No REST for the Wicked.\",\r\n       \"description\": \"Creating of an issue using IDs for projects and issue types using the REST API\",\r\n       \"issuetype\": {\r\n          \"id\": \"<ISSUE-TYPE-ID>\"\r\n       }\r\n   }\r\n}<\/pre>\n
        \"odbc-Issue<\/a>
        Issue Input Data<\/p><\/div><\/li>\n
      6. Now using Query Builder we will generate the query.\n
      7. Now Let’s Test Connection and it will prompt the message “Connection is OK!”<\/li>\n
      8. That’s it we have successfully configured JIRA Rest API in ODBC JSON Driver.<\/li>\n<\/ol>\n
        Setup Linked Server in MS SQL Server for JIRA<\/span><\/h3>\n
        Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API Calls.<\/p>\n
          \n
        1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here:\u00a0Get SQL Server Express<\/a>\u00a0and\u00a0\u00a0Get SSMS<\/a><\/li>\n
        2. Open SSMS and connect to SQL Server.<\/li>\n
        3. Go to Root > Server Objects > Linked Servers node. Right click and click\u00a0New Linked Server…<\/strong>\n
          \n
          \"Add<\/a><\/p>\n
          Add Linked Server in SQL Server<\/p>\n<\/div>\n<\/li>\n
        4. \u00a0Now enter the linked server name, select Provider as SQL Native Client<\/li>\n
        5. Enter data source as\u00a0GatewayServerName, PORT_NUMBER<\/span><\/strong>\u00a0where 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.<\/li>\n
        6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column\n
          \n
          \"Configure<\/a><\/p>\n
          Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection<\/p>\n<\/div>\n<\/li>\n
        7. Click on Security Tab and select last option “Be made using this security context<\/strong>“. Enter your gateway user account here.<\/li>\n
        8. Click OK to save Linked Server<\/li>\n<\/ol>\n
          Insert data in JIRA from SQL Server (Call POST API Method)<\/h3>\n
            \n
          1. To Insert the issue in JIRA we need to execute the query like this. Please refer to this link for more information: Creating an issue example.<\/a>
            \n
            Important Note*: If you don’t pass META='{xxxx}’ in query driver call REST API 2 times first time to parse metadata and second time to get actual data. So that you find some odd thing in JIRA\u00a0project if you execute the query without Meta then you will find two same issues created in your JIRA project as the driver makes two calls as mentioned above.<\/div><\/div>\n
            SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME], \r\n'SELECT * FROM $\r\nWITH( META=''[{\"Name\": \"Success\",\"Type\": \"Int32\"}]''\r\n     ,Src=''https:\/\/zappysys2.atlassian.net\/rest\/api\/2\/issue\/''\r\n     ,RequestData=''{\r\n       \"fields\": {\r\n        \"project\":\r\n        {\r\n          \"id\": \"10110\"\r\n        },\r\n        \"summary\": \"No REST for the Wicked.\",\r\n        \"description\": \"Creating of an issue using IDs for projects and issue types using the REST API\",\r\n        \"issuetype\": {\r\n          \"id\": \"1\"\r\n       }\r\n     }\r\n}''\r\n,Header=''Content-Type: application\/json || Accept: *\/*''\r\n,RequestMethod=''POST''\r\n)')<\/pre>\n<\/li>\n
          2. Here is the preview after you run REST API query in SQL Server. It will return created issue id, key, and Link. Now let’s note down this response to update and delete the same issue if you want to. Notice that you can override default configuration by supplying\u00a0many parameters<\/a>\u00a0in WITH clause.\n
          3. You can also find this created issue in your JIRA project.\n
            \"jira-issue-preview-in-browser\"<\/a>
            Created Issue in JIRA<\/p><\/div><\/li>\n<\/ol>\n
            Update data in JIRA from SQL Server (Call PUT API Method)<\/h3>\n
              \n
            1. Let’s update the issue which we created in the previous step using id. I Added Extra Meta details in Query to prevent driver to make two calls and also to prevent error as JIRA Rest API Response is a blank Status code of “204 No Content”<\/span>. Please refer to this link for more information: Updating an Issue via the JIRA REST APIs<\/a>\n
              SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]\r\n, 'SELECT * FROM $\r\n   WITH(META=''[{\"Name\": \"Success\",\"Type\": \"Int32\"}]'' ,\r\n\t Src=''https:\/\/zappysys2.atlassian.net\/rest\/api\/2\/issue\/10391''\r\n\t,DataConnectionType=''HTTP''\r\n\t,RequestData=''{\r\n    \"fields\": {\r\n       \"summary\": \"Updated One!...Ye merry gentlemen.\"\r\n   }\r\n}''\r\n\t,Header=''Content-Type: application\/json || Accept: *\/*''\r\n\t,RequestMethod=''PUT''\r\n)')\r\n<\/pre>\n<\/li>\n
            2. Let’s is the preview after you running update query in SQL Server.\n
            3. Now, Let’s see the updated issue in JIRA project.\n
            4. One Import note if you don’t add that metadata then you will get an error like below as JIRA Rest API Response is a blank “Status code of ‘204 No Content'”. If you don’t set META the REST API Make Two calls First to parse metadata and for the second time to get actual data.
              \n
              Query execution error: No data returned for specified table name or filter expression. Make sure table name or filter is typed correctly (it is case sensitive) and input data contains a corresponding element(s).<\/div><\/div><\/li>\n<\/ol>\n
              Delete data in JIRA from SQL Server (Call DELETE API Method)<\/h3>\n
                \n
              1. Now let’s delete the issue which we created by executing following query in SQL Server. We also need to add extra meta details in query same as update one as JIRA Rest API Response is a blank Status code of “204 No Content”<\/span>. Please refer to this link for more information: Delete an Issue via the JIRA REST API<\/a>\n
                SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME]\r\n, 'SELECT * FROM $\r\n   WITH(META=''[{\"Name\": \"Success\",\"Type\": \"Int32\"}]'' ,\r\n\t Src=''https:\/\/zappysys2.atlassian.net\/rest\/api\/2\/issue\/10391''\r\n\t,DataConnectionType=''HTTP''\r\n\t,Header=''Content-Type: application\/json || Accept: *\/*''\r\n\t,RequestMethod=''DELETE''\r\n)')\r\n<\/pre>\n<\/li>\n
              2. Now, Let’s check the deleted issue in JIRA project.\n
                \"deleted-issue-in-jira\"<\/a>
                Issue Deleted in JIRA Project<\/p><\/div><\/li>\n<\/ol>\n
                Create multiple JIRA\u00a0issues using SQL Server Stored Proc<\/h3>\n
                Now lets put all previous things to more practical use. We will create a stored procedure which will read Issues stored in a SQL Table and Submit it to JIRA to create new Issue via REST API call. This will make it totally dynamic so SQL Table can act as your Source of information for API call.<\/p>\n
                  \n
                1. Let’s create stored procedure to read data from “tblIssues” data table and create bulk issues in JIRA.\n
                  alter PROCEDURE usp_AddMultipleJiraIssues\r\nAS\r\nBEGIN\r\n\r\nDeclare @issueTemp nvarchar(2000)\r\nDeclare @PostQuery nvarchar(MAX)\r\nDeclare @strIssues nvarchar(MAX)\r\nset @PostQuery='';\r\nset @strIssues='';\r\n\r\n---- Template \r\nset @issueTemp=\t'{\r\n\t\t\t\"fields\": {\r\n\t\t\t\t\"project\": {\r\n\t\t\t\t\t\"key\": \"<YOUR-JIRA-PROJECT-KEY>\"\r\n\t\t\t\t},\r\n\t\t\t\t\"summary\": \"[ISSUE-SUMMARY]\",\r\n\t\t\t\t\"description\": \"[ISSUE-DESCRIPTION]\",\r\n\t\t\t        \"issuetype\": {\r\n\t\t\t\t\t  \"id\": \"<YOUR-JIRA-ISSUE-TYPE>\"\r\n\t\t\t\t}\r\n\t\t\t}\r\n\t\t}';\r\n\r\n-----Generate issues JSON String from the data table \r\nSelect @strIssues = @strIssues\r\n                    + REPLACE(REPLACE(@issueTemp,'[ISSUE-SUMMARY]',isnull(i.summary,''))\r\n                                             ,'[ISSUE-DESCRIPTION]',isnull(i.description,''))\r\n                    + ',' \r\nfrom tblissues i\r\n\r\n-----Trim last , comma from the string otherwise you will get an error \r\nSET @strIssues = LEFT(@strIssues,(LEN(@strIssues)-1))\r\n\r\n-----JIRA Post Query\r\nSET @PostQuery= 'SELECT * FROM OPENQUERY([UPDATE_JIRA_DATA_JSON_DRIVER]\r\n\t\t\t\t\t, ''SELECT * FROM $\r\n\t\t\t\t\tWITH(\r\n\t\t\t\t\t\tMETA=''''[{\"Name\": \"Success\",\"Type\": \"Int32\"}]''''\r\n                                                ,Src=''''https:\/\/zappysys2.atlassian.net\/rest\/api\/2\/issue\/bulk''''\r\n\t\t\t\t\t\t,DataConnectionType=''''HTTP''''\r\n\t\t\t\t\t\t,RequestData=''''{\r\n\t\t\t\t\t\t   \"issueUpdates\": [' + @strIssues + ']\r\n\t\t\t\t\t        }''''\r\n\t\t\t\t\t\t,Header=''''Content-Type: application\/json || Accept: *\/*''''\r\n\t\t\t\t\t\t,RequestMethod=''''POST''''\r\n\t\t\t\t\t)'')'\r\n\r\n--Print(@PostQuery)\r\n\r\n----Posting issues data in JIRA\r\nExecute(@PostQuery)\r\n\r\nEND\r\nGO\r\n<\/pre>\n<\/li>\n
                2. That’s it now executes the stored procedure and you will find bulk issues created in your JIRA project.\n
                  \"Bulk<\/a>
                  Bulk Issues Created In Jira<\/p><\/div><\/li>\n<\/ol>\n
                  Conclusion<\/span><\/h2>\n
                  In this blog, we learned how to generate a JIRA API Token and how to configure it,\u00a0 Insert, Update and delete Issue in JIRA using\u00a0ODBC JSON \/ REST API Driver<\/a> from the SQL server. You can achieve many more functionalities with this tool. Check our blogs\/articles on JSON \/ REST API Driver<\/strong><\/a> to find out what\u00a0this tool<\/em>\u00a0is capable of more.<\/p>\n
                  References<\/span><\/h2>\n
                  Finally, you can use the following links for more information about the use of JIRA API with our tools:<\/p>\n