Download Tickets Using Data Factory

[Glyn Thomas]

Hi,

I've created a linked service from our data factory through to jira as we want to download the jira tickets into the warehouse and do some reporting on them. I can see the jira objects however previewing the tables either come back blank or I don't get a preview at all.

How do I tell which object the data for our tickets is stored in so that I can download and process them?

Kind Regards

Glyn

[Glyn Thomas · Accepted solution]

We've managed to get it working. I'll detail the steps below:

Create a user in Jira

Create an api token in jira under that account.

The user must have the role of Service Desk Team assigned to the project you are trying to access. We were trying to get the PIH tickets so we needed the user to have these perms assigned to that project.

Go into adf and create a new jira linked service

Host: http://myorg.atlassian.net/

User Name: Email address of the account

Password: API Key that was created (it's better to set up a key vault secret for this)

Test the connection then save it.

Go to the copy activity wizard in adf.

Select the new jira linked service as the source.

When the list of "Tables"/Objects appears the issues for us resided in "Platform"."Api_Issue"

Continue with the wizard and setup a sink etc.

Run the pipeline (Hopefully you should now have the data).

[Nic Brough -Adaptavist-]

I'm a little uncertain on this one.

Is your "data factory" something that tries to read a database?  If so, I'm stuck because you've tagged this with "Cloud" and you don't have any access to the database on Cloud systems.

Can you confirm you are on Cloud?  (The Jira url will be something like yourplace.atlassian.net )

[Glyn Thomas]

Hi Nic,

It's capable of reading nearly any data source. I believe the connection specifically for jira uses the rest api and shows the data that comes back in a structured format like a table (but the source of the table isn't). When using the linked service we can see some of the data, however most comes back as just the structure/meta data, the other data seems to be hidden (I'm assuming because the way we're trying to authenticate it is incorrect). We are using cloud though.

The data factory also has a rest api connector which I've been trying to use, it has 5 different auth types Anonymous, Basic, ADD Service Principle, Manage Identity, User Defined Managed Identity. The default is basic, but from reading the jira manuals I can see that basic auth is no longer a valid option. I'm kind of thinking we could connect azure to jira using an ADD service principle and generate an Oauth token to access it that way.

Are you able to confirm 2 things:

Is the base url for jira http://myorg.atlassian.net/rest/api/2

Is the api call to get all issues https://myorg.atlassian.net/rest/api/2/search?jql=

I'll be honest, my knowledge of how to use rest api's is not great as I'm primarily a DBA/SQL/ETL dev so I haven't had to use them that much before.

Kind Regards

Glyn

[Nic Brough -Adaptavist-]

Ok working backwards as your later questions are easier:

Yes, you can search that way, but you will need to start paging through the results if you've got more than 1,000 issues (and bear in mind your data is obsolete the instant you've scraped it)

The "base url" for a Cloud Jira is https://myorg.atlassian.net/ - the rest of what you've got there is the definition of the (older) rest api.

Yes, the only way you can currently connect to a Cloud Jira without the UI is the REST API.  You will need to use oauth or basic authentication

"The other data seems to be hidden" - I suspect this is basically the connector not asking the right questions, not having the right "table" definitions or not understanding the JSON coming back