Page tree
Skip to end of metadata
Go to start of metadata

About this module

This module shows you how to automatically manage data updates in a .csv file stored on Dropbox and synchronise the changes to a SQL Server database at specific times, using Universal Platform schedules.
Module 2 of this tutorial explained how to manually manage data updates in a .csv file and synchronise the changes to a database, using Universal Platform constraints.
So what does this module cover? The scenario is this:

In Module 1, Harmony, a member of the HR team, was required to capture all company staff records in a SQL database and ensure that all changes to staff information was subsequently updated in the database. 
 
 Harmony does not have any SQL database experience but does know how to work with a .csv file. So, Harmony was happy to add staff information to a .csv file but asked Bob from the IT Department to assist her with importing the data to the database. As Harmony works from a number of different global locations, the solution was to enable Harmony to add staff records to the HR.csv file on Dropbox, and Bob would then use Universal Platform to import the data to the database. 
 
 At the end of Module 1, Harmony and Bob had successfully imported the data in HR.csv to the SQL database, using Universal Platform. 
 
 In Module 2, Harmony and Bob were required to manage any changes to staff records in HR.csv and ensure only the updated information is imported to the database. At the end of Module 2, Harmony and Bob had successfully imported only changes to the data to the database, using Universal Platform's constraints. 
 
 Harmony and Bob now need to find a way to automatically update any changes to staff records in HR.csv to the database. Bob recommends using Universal Platform's scheduled jobs to facilitate this. 
 
 Your task is to help Bob by following Module 3 to ensure that changes to the data in HR.csv is automatically imported to the database, according to a set schedule.


Module 3 of this tutorial explains how to configure schedules to trigger the import of data in a .csv file from Dropbox to a SQL database, to ensure any updates are automatically applied at set intervals. In this module:

  • You will start by enabling the scheduler service on Universal Platform.
  • You will then configure a job to define the function of the job and the intervals that you want the job to automatically run, when the schedule is enabled.
  • You will then manually run the job to import data from the HR.csv file to your database table and check that there are no errors.
  • Once the job is configured and executed successfully, you will enable the schedule to automatically run the job at the specified times.
  • You will complete the module by viewing a list of all active scheduled jobs.


Now that you are familiar with what we are going to do in this module, let's begin!

 

 Enabling the Universal Platform scheduler service

Before you can schedule a job to automatically import data from a .csv file on Dropbox to a SQL Server database at specific times, you must turn Universal Platform's scheduler service on. This service facilitates the automatic execution of jobs according to the schedules set in each job. 

To enable the scheduler service:

  1. Open Universal Console in a browser tab.
  2. Select Schedule. The Scheduler screen is displayed.
  3. Configure the scheduler as follows:

    • Enable on Start?: On

  4. Click Apply.
  5. Click Start.



Now that the scheduler service is started, you can configure the job to automatically import data from a .csv file on Dropbox to a SQL Server database at specific times. 

 Configuring the job

To schedule automatic updates of a .csv file to your database, you must configure a job that runs at a set interval. A job is a series of operations performed by the SQL Server Agent sequentially. A job can perform different activities, such as executing a pipeline you have created. A job can also be scheduled so that it automatically executes at specified times.

In this example you are going to create a job that executes every 5 minutes and runs the pipeline you created in Module 1, to populate your database table with the data in HR.csv. If you are working through this module on your own, you should now be working with HRNew.csv


To do this:

  1. Open Information Bus in a browser tab.
  2. Select Jobs. The Jobs screen is displayed.
  3. Click Add.
  4. Select Execute Pipeline from the list of extensions. The New Execute Pipeline screen is displayed.
  5. On the General tab, enter a name for the job in the format: Job_Pipeline_CSV.
  6. Select Hour and each 5 minute interval, from the Schedule list in the Job section. Note:The schedule is set according to each 5 minute interval in an hour. To configure this, you must select each 5 minute instance in the hour. To do this, you must select the options 051015 and so on.
  7. Click the Pipeline_CSV pipeline you created in Module 1 of this tutorial, from the Pipeline list in the Job Parameters section.
  8. Click Save & Close. The Jobs screen is displayed and the job you created is available in the list of jobs.

 Manually running the job

You are now ready to manually execute the job to run the pipeline and import the data in the HR.csv file to your SQL database.

 

To do this:

  1. On the Jobs screen, click the Actions button for the Job_Pipeline_CSV job you created.
  2. Click Run. A confirmation message is displayed.
  3. Click Run to confirm executing the job.



    Next you must check that the job ran successfully.

  4. Click the Job_Pipeline_CSV job you created. The Job Details screen is displayed.
  5. Select the History tab. Check the messages to ensure that the job ran successfully without any failures.
  6. Click Close.

 Viewing the data imported to the database table by a job

Now that you have configured a job and checked that it runs successfully, let's see how the job synchronises updates to information in the .csv file.

In this example, Harmony Osullivan has changed her phone number. This is updated in HR.csv. If you are sitting in instructor-led training, your presenter will now make changes to employee information in HR.csv and update the file on Dropbox. If you are working through this module on your own, please skip this section and go to the Updating the .csv file on Dropbox section to import the updated HRNew.csv file.
Now that HR.csv is updated, you must run the job again and import the data in the file to your SQL database. 


To do this:

  1. Run the Job_Pipeline_CSV job you created, as explained in the Manually running a job section.


Now let's check that the data in the .csv file imported to your database table. 

 

To do this:

  1. Go to the browser tab that has Data Manager open.
  2. Select Queries. The Queries screen is displayed.
  3. Click the CheckTable_DataHub SQL query you created in Module 1 to check that the data in the .csv file imported to your database table correctly. The Query Details screen is displayed.
  4. Select the Editor tab and ensure the following query is displayed: 
    Select * from DataHub
     
  5. Click Run to execute your query and view the results displayed in the Output pane. Harmony's phone number should now be changed to the new value.


 Scheduling a job

Now that you have created a job and successfully executed it, you can enable the schedule so that the job automatically executes at specified times, according to the schedule interval defined in the job. 

To do this:

  1. On the Jobs screen, click the Actions button for the Job_Pipeline_CSV job you created
  2. Click the Actions button for the job you created.
  3. Click Schedule. A confirmation message is displayed.
  4. Click Schedule to confirm enabling the schedule. The job is now configured to run automatically, every 5 minutes to import the data in the HR.csv file to your SQL database.

 Viewing scheduled jobs

You can view a list of jobs with active schedules to see more information about the job or to edit the job's details. 

To view a list of scheduled jobs:

  1. Go to the browser tab that has Information Bus open.
  2. Select Schedules. The Schedules screen is displayed listing all jobs scheduled to automatically run at specific times.


 And that's a wrap

And that's a wrap - congratulations on completing this module! Before we finish, let's have a quick look at what you have done: 


The next part of this tutorial, Module 4, explains how to manage conflicting data between the .csv file and the database, when synchronising to the database.

 

 

  • No labels