Kepion + SQL Agent + PowerShell

By - Updated November 19, 2018

In this article, we will explore how to integrate Kepion together with a SQL Agent Job and a PowerShell script.  We will explore these concepts with an example showing how to automate a file cleanup process for a given file store.

Here is the general outline:

  1. Create a SQL Agent Job.
  2. Add PowerShell script to a SQL Agent Job.
  3. Configure PowerShell script to perform the following:
    1. Read data from a SQL table.
    2. Iterate over table result and call the Kepion Web Service.
    3. Write results back to a SQL table.

Before We Get Started

For this example, you will need to identify the following:

  1. <KEPION_APPLICATION>: An application with the File Store functionality implemented.
  2. <FILE_STORE>: A File Store for use by the example.  WARNING: files will be deleted if you use valid file ids.

1 – Setup Application

  1. Create the following table on the application database:

  2. Identify the file ids that you want to tag for removal:

    Insert the ids for the files that you want to remove into the table (note: if you use invalid ids, no files will be affected):

2 – Setup PowerShell

Replace the <KEPION_APPLICATION>, <FILE_STORE> and URL for the Kepion web service with appropriate values.

You can test out the script with PowerShell:

For example:

You can re-run the example multiple times by re-inserting into the table the files tagged for removal:

3 – Setup SQL Agent:

  1. Create Credential for Kepion SI (Service Identity)

    Navigate to your SQL Server instance’s Security->Credentials folder:

    Create a credential for Kepion SI (Service Identity).  This identity will be used to connect into Kepion.

    IMPORTANT:  Please ensure the Kepion SI credential has appropriate security roles within Kepion. (i.e. grant Kepion SI as System Admin or as Modeler).

  2. Setup Proxy
  3. Create Job
  4. Add Step to Job

    Replace the <POWERSHELL_SCRIPT> with your tested script.

Now that the SQL Agent Job is configured, you can call it from within SQL using: