{"id":4266,"date":"2018-07-03T13:32:03","date_gmt":"2018-07-03T13:32:03","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4266"},"modified":"2025-03-19T12:47:56","modified_gmt":"2025-03-19T12:47:56","slug":"read-jira-data-ssis-call-rest-api-load-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-jira-data-ssis-call-rest-api-load-sql-server\/","title":{"rendered":"How to read JIRA data in SSIS – Call REST API \/ Load to SQL Server"},"content":{"rendered":"

Introduction<\/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 SSIS<\/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\"jira-logo\"<\/a>In this blog, we will learn\u00a0How to read JIRA data in SSIS and load into SQL Server Table<\/strong>, along with few other topics such as how to generate an API Token using Atlassian Account for JIRA REST API Call<\/strong>, how to read all Issues data from JIRA with SSIS.<\/p>\n

We will go through the steps to read data from JIRA and Load into SQL Server using SSIS JSON Source Connector.<\/a><\/p>\n

In nutshell,\u00a0this post will focus on how to call JIRA API using SSIS.<\/p>\n

So let’s get started.<\/p>\n

Prerequisite
\n<\/span><\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/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

    In order to start, we will show several examples. ZappySys includes an\u00a0SSIS JSON Source Connector<\/a>\u00a0that will help you to call JIRA API, read various data from JIRA with SSIS and do many more actions using REST API. 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

      Load JIRA data to SQL Server using SSIS JSON Source<\/h3>\n

      Now once you have API token in the JIRA Atlassian Cloud website we can move to SSIS Part.<\/p>\n

        \n
      1. Create a new SSIS Project or Open Existing Project in Visual Studio\u00a0 (File > New > Project > Business Intelligence > Integration Services)<\/li>\n
      2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS JSON Source Component<\/a> from the SSIS Toolbox.\n
      3. Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
        \nEnter Web URL like this:<\/p>\n
        https:\/\/your-domain.atlassian.net\/rest\/api\/2\/search?startAt=0&maxResults=50\r\n<\/pre>\n
        Here,\u00a0your-domain is your Atlassian Cloud account\u2019s domain name (we are using zappysys.atlassian.net).<\/strong><\/p>\n
        * startAt<\/strong>\u00a0is the index of the first item returned in the page of results.
        \n* maxResults<\/strong>\u00a0the maximum number of items that can be returned per page. 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\u00a0        JIRA API Documentation for Pagination<\/a>)<\/p>\n
        \"JSON<\/a>
        JSON Source: Enter the Request URL<\/p><\/div><\/li>\n
      4. Check Use Credentials Option and select a\u00a0new ZS-HTTP Connection from the drop-down.<\/li>\n
      5. Configure new HTTP Connection for JIRA as below.
        \nBasically, you need to enter 3 pieces on the General tab (i.e. Auth URL, Atlassian Account UserName, API token)<\/p>\n
      6. now, we are going to set Pagination related configurations for JIRA Search request. For example, if you want to read 50 records in each response and keep doing it until last page found then set startAt=0, maxResults=50 and increment startAt by 50<\/b>.
        \n
        Do not to submit your App for Marketplace Approval (Last step)\u00a0if you are creating OAuth App to access their own company data and do not want to access other company’s data using your app. In other words leave your app under Development mode only (Ignore production credentials if you do not wish to publish app to marketplace).<\/div><\/div>\n
      7. Select Filter, Click on the Preview button to see the response data.\n
      8. Click OK to save JSON Source settings.<\/li>\n
      9. Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load JIRA Issues data into SQL Server Table.\n
      10. Configure OLEDB Destination (Select Connection and Target Table). You can Create a new Table for the destination by clicking NEW next to Table drop-down.\n
      11. Click on the Columns Tab to configure Mappings. Click OK to save.\n
      12. You can see the requests in Fiddler<\/a>.\n
      13. And that’s it. Execute your package to Load JIRA data to SQL Server.<\/li>\n<\/ol>\n
        Conclusion<\/span><\/h2>\n
        In this blog, we learned how to Generate a JIRA API Token, retrieve JIRA Issues\/tickets data from JIRA with SSIS using\u00a0ZS JSON Source Component<\/a>\u00a0in a very simple way. You can achieve many more functionalities with this tool. Check our blogs\/articles on\u00a0JSON Source Component\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-json-source\/<\/a>\u00a0to 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