Skip to main content

 

Data integration and automation have become crucial for businesses that rely on seamless data flows between different platforms. In this article, we’ll walk you through building and automating a real-time data pipeline to transfer work items from Azure DevOps to SharePoint, using CData JDBC drivers, Java, and automation tools such as Windows Task Scheduler, Cron Jobs, and Jenkins. This approach helps ensure your data is always up to date, without the need for manual intervention.

Tags: Automation, CData driver, JDBC

 

Why Automate Data Pipelines?

Before we jump into the technical setup, let’s first understand why automating data pipelines is so important:

  • Real-time Data Availability: Automation ensures that your data is transferred and synchronized in real-time across platforms.
  • Reduced Human Error: By eliminating manual processes, the chances of errors are minimized.
  • Efficiency and Time Savings: With automation in place, you can free up valuable time and resources for more strategic tasks.
  • Scalability: As your business grows, automated pipelines can handle an increasing amount of data with minimal changes to the workflow.

Let’s get started with the setup!

 

Step 1: Setting Up CData JDBC Drivers

The first step in the process is to set up the CData JDBC Drivers. These drivers make it simple to connect to and interact with various data sources like Azure DevOps and SharePoint.

1.1 Download CData JDBC Drivers

  1. Go to CData’s Website: Navigate to the CData website and download the appropriate JDBC drivers for Azure DevOps and SharePoint.
  2. Install the Drivers: Follow the installation instructions provided by CData to install the drivers on your machine.

 

 

Step 2: Write the Java Code for Data Transfer

Now that the drivers are set up, let’s write the Java code that will connect to Azure DevOps, fetch data (work items), and then insert that data into SharePoint.

 

2.1 Prepare the Java Environment

  1. Ensure that you have Java installed on your system. You can download the latest version of Java from various suppliers, such as Adoptium’s Eclipse Temurin.
  2. Set up your Java project using an IDE like IntelliJ IDEA or Eclipse.
  3. Add the CData JDBC driver JAR files to your project’s classpath. In IntelliJ IDEA, this can be done by navigating to File > Project Structure > Libraries > + > Java and selecting the downloaded JAR files for Azure DevOps and SharePoint.

 

2.2 Java Code Example

Here’s a more detailed example of the Java code that connects to Azure DevOps, fetches work items, and then inserts the data into SharePoint.

Explanation:

  • We connect to Azure DevOps and SharePoint using JDBC.
  • We fetch work items from Azure DevOps that are in specific states.
  • We insert the work items into a SharePoint List.

 

2.3 Compile and Package the Java Code

Once your code is ready, the next step is to compile it and generate an executable JAR file.

  1. Build the Artifact: In IntelliJ IDEA, go to File > Project Structure > Artifacts > + > JAR > From modules with dependencies. Choose the DataPipeline class as the main class.
  2. Build the JAR: Go to Build > Build Artifacts > Build to compile and package your project into a JAR file. The artifact will be located under the out/artifacts directory in your project.

 

import java.sql.*;
public class Main {
    public static void main(Stringl] args) {
        // Define JDBC connection URLs for Azure DevOps and SharePoint
        String azureDevOpsUrl = "<your azuredevops connection string>";
        String sharePointUrl = "<your SharePoint connection string>";
        try (
                // Connect to Azure DevOps
                Connection azureDevOpsConnection = DriverManager.getConnection(azureDevOpsUrl);
                // Connect to SharePoint
                Connection sharePointConnection = DriverManager.getConnection(sharePointUrl)
        ) {
            String azureQuery = "SELECT aId], nTitle],  State], �AssignedToDisplayName] FROM Project_testcdata.Project.WorkItems WHERE lState] IN ('Proposed', 'Design')";
            try (Statement stmt = azureDevOpsConnection.createStatement();
                 ResultSet rs = stmt.executeQuery(azureQuery)) {
                while (rs.next()) {
                    int workItemId = rs.getInt("Id");
                    String title = rs.getString("Title");
                    String state = rs.getString("State");
                    String assignedToDisplayName = rs.getString("AssignedToDisplayName");
                    // Check if the WorkItemId already exists in SharePoint
                    String checkQuery = "SELECT COUNT(*) FROM manidevopslist WHERE WorkItemId = ?";
                    try (PreparedStatement checkStmt = sharePointConnection.prepareStatement(checkQuery)) {
                        checkStmt.setInt(1, workItemId);
                        try (ResultSet checkRs = checkStmt.executeQuery()) {
                            checkRs.next();
                            int count = checkRs.getInt(1);
                            // If WorkItemId doesn't exist, insert it into SharePoint
                            if (count == 0) {
                                String insertQuery = "INSERT INTO manidevopslist (Title, WorkItemId, State, AssignedToDisplayName) VALUES (?, ?, ?, ?)";
                                try (PreparedStatement pstmt = sharePointConnection.prepareStatement(insertQuery)) {
                                    pstmt.setString(1, title);
                                    pstmt.setInt(2, workItemId);
                                    pstmt.setString(3, state);
                                    pstmt.setString(4, assignedToDisplayName);
                                    pstmt.executeUpdate();
                                }
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

Step 3: Automate the Data Pipeline

Now that we have our JAR file, we can automate its execution. There are multiple ways to do this, depending on your environment:

 

3.1 Automate with Windows Task Scheduler

For Windows, you can use the Task Scheduler to schedule your JAR file to run at specific intervals.

Steps:

  1. Open Task Scheduler: Press Windows + R, type taskschd.msc, and press Enter to open the Task Scheduler.
  2. Create a New Task: Click Create Task on the right-hand side.
  3. Set Triggers: Under the Triggers tab, click New to set the schedule for the task. For example, you can set it to run daily at 9 AM.
  4. Set Actions: Under the Actions tab, click New, then:
    • Program/script: Enter cmd.exe.
    • Arguments: Type the following: 
      /c "java -jar C:\path\to\DevopstoSharepoint.jar"
  1. Save the Task: Click OK to save the task. The scheduled task will now run according to the trigger you’ve set.

 

 

3.2 Automate with Cron Jobs (Linux/macOS)

For Linux or macOS, you can automate the execution with Cron Jobs.

Steps:

  1. Open the Crontab Editor: In your terminal, type: crontab -e
  2. Add a Cron Job: Add the following line to schedule your task. For example, to run the JAR file daily at 9 AM: 
    0 9 * * * /usr/bin/java -jar /path/to/DevopstoSharepoint.jar
  3. Save and Exit: Save the file and exit the editor. The cron job will now run at the specified time.

3.3 Automate with Jenkins

If you’re using Jenkins for CI/CD, you can easily set up an automated job to execute your JAR file.

Steps:

  1. Create a New Job in Jenkins: Open Jenkins and click New Item > Freestyle Project.
  2. Configure the Build Step: In the Build section, select Execute Shell (for Linux) or Execute Windows batch command (for Windows).
    • For Windows, use: 
      java -jar C:\path\to\DevopstoSharepoint.jar
    • For Linux, use: 
      /usr/bin/java -jar /path/to/DevopstoSharepoint.jar
  1. Schedule the Job: In the Build Triggers section, select the schedule for the job (e.g., run every day at 9 AM).
  2. Save the Job: Click Save, and Jenkins will automatically run the pipeline based on the schedule.

 

 

Step 4: Monitor and Maintain the Pipeline

4.1 Logs and Notifications

For ongoing operations, you may want to set up logging and notification systems to ensure that the pipeline runs smoothly:

  • Logging: You can configure logging in Java to track errors and exceptions in the pipeline execution.
  • Notifications: Tools like Jenkins can send notifications via email or Slack in case of job failures.

 

4.2 Error Handling and Debugging

Ensure that your pipeline is resilient to common issues like connection timeouts or data discrepancies. Implement try-catch blocks in your Java code to handle exceptions gracefully.

 

Conclusion

In this article, we demonstrated how to automate a real-time data pipeline using CData JDBC Drivers, Java, and automation tools such as Windows Task Scheduler, Cron Jobs, and Jenkins. This solution enables seamless integration between Azure DevOps and SharePoint, ensuring that data is consistently transferred and up-to-date without the need for manual intervention.

By leveraging CData JDBC Drivers, we simplified the connection and interaction with both Azure DevOps and SharePoint. Additionally, automation through Task Schedulers and CI/CD tools ensures that the data pipeline runs reliably on a scheduled basis, improving operational efficiency, reducing the risk of errors, and allowing teams to focus on more strategic tasks.

Implementing this solution not only streamlines data workflows but also provides scalability as your integration needs grow. Whether you're working in a Windows, Linux, or cloud-based environment, the flexibility of this approach ensures that your data integration remains robust and future-proof.

This end-to-end automation process is crucial for organizations looking to enhance their data operations, improve collaboration, and maintain a real-time flow of information across critical platforms.

Terms and ConditionsCookie settings