Jira Connector for SSIS : Read worklogs modified after a specified date

Name: SSIS PowerPack
Rating: 4.8 (107 reviews)
Author: ZappySys

Learn how to read worklogs modified after a specified date using the Jira Connector for SSIS. This connector enables you to read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required. We'll walk you through the exact setup.

Let's dive in!

Jira Connector

Powering this integration

Low-code / No-code
High performance
Bidirectional
Easy to use

Built on the ZappySys API Connector Framework, the Jira Connector for SSIS is a high-performance, low-code / no-code solution. It enables you to read and write Jira data without any programming right in your SSIS package. Part of the SSIS PowerPack.

Documentation

Video tutorial

Watch this quick video to see the integration in action. It walks you through the end-to-end setup, including:

Installing the SSIS PowerPack
Configuring a secure connection to Jira
Working with Jira data directly inside SSIS
Exploring advanced API Source features

While this video uses the OData Connector as an example, the core concepts and setup process are exactly the same for the Jira Connector.

Watch this video on YouTube

Once you are done watching, simply follow the step-by-step written guide below to configure your data source.

Prerequisites

Before we begin, make sure the following prerequisites are met:

SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

Read worklogs modified after a specified date in SSIS

Open Visual Studio and click Create a new project.
Select Integration Services Project. Enter a name and location for your project, then click OK.
From the SSIS Toolbox, drag and drop a Data Flow Task onto the Control Flow surface, and double-click it:
Make sure you are in the Data Flow Task designer:
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
Select New Connection to create a new connection:
Use a preinstalled Jira Connector from Popular Connector List or press Search Online radio button to download Jira Connector. Once downloaded simply use it in the configuration:

Jira

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

Configure the authentication in Jira.
Enter those details into the API Connection Manager configuration.

API Key based Authentication

Jira authentication

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

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

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

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

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:

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

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:

Go to Atlassian Developer area.
Click Create and select OAuth 2.0 integration item to create an OAuth app:
Give your app a name, accept the terms and hit Create:
To enable permissions/scopes for your application, click Permissions tab, then hit Add button, and click Configure button, once it appears:
Continue by hitting Edit Scopes button to assign scopes for the application:
Select these scopes or all of them:
Then click Authorization option on the left and click Add button:
Enter your own Callback URL (Redirect URL) or simply enter https://zappysys.com/oauth, if you don't have one:
Then hit Settings option and copy Client ID and Secret into your favorite text editor (we will need them in the next step):
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).
Click Generate Token to generate tokens.
Finally, select Organization Id from the drop down.
That's it! You can now use Jira Connector!

API Connection Manager configuration

Just perform these simple steps to finish authentication configuration:

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

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.

Select Read Worklogs modified after a specified date endpoint from the dropdown and hit Preview Data:

API Source - Jira

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

Jira

Read Worklogs modified after a specified date

Required Parameters
Worklog Modified After Date/Time	Fill-in the parameter...
Optional Parameters
JQL Expression (For Issue Search)	created >= 1970-01-01
Project Key(s) - Comma Separated (CBS,BRK)
Issue Key(s) or Ids - Comma Separated (CBS-10,PRA-13)

SSIS API Source - Read from table or endpoint

That's it! We are done! Just in a few clicks we configured the call to Jira using Jira Connector.

You can load the source data into your desired destination using the Upsert Destination , which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV , Excel , Azure Table , Salesforce , and more . You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)

Conclusion

And there you have it — a complete guide on how to read worklogs modified after a specified date in SSIS without writing complex code. All of this was powered by Jira Connector, which handled the REST API pagination and authentication for us automatically.

Download the trial now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

Download Chat

More actions supported by Jira Connector

Got another use case in mind? We've documented the exact setups for a variety of essential Jira operations directly in SSIS, so you can skip the trial and error. Find your next step-by-step guide below: