Integrate SQL Agent Job and Powershell

Author: Kepion Product Team

Introduction

In this article, we'll 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 You Start

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.

 

Note: Files will be deleted if you use valid file ids.

 

 

Setup Application

Step 1. Create the following table in the application database:

 

CREATE TABLE [dbo].[USR_DELETE_FILES](
  [FileStoreFileId] [int] NOT NULL
)
ON [PRIMARY] 

 

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

 

SELECT * FROM [dbo].[FileStore_...]

 

Step 3. Insert the ids for the files that you want to remove into the table:

 

INSERT INTO [dbo].[USR_DELETE_FILES]
(FileStoreFileId) VALUES(1);
 
INSERT INTO [dbo].[USR_DELETE_FILES]
(FileStoreFileId) VALUES(2); 

 

Tip: If you use invalid ids, no files will be affected.

 

 

Setup PowerShell

Replace the <KEPION_APPLICATION>, <FILE_STORE>, and URL for the Kepion Web Service with appropriate values.

 

#Create SQL connection string
$connectionString = "Data Source=.;Initial Catalog='<KEPION_APPLICATION>';Integrated Security=SSPI;Persist Security Info=False;Application Name='Kepion Customization'"
$sqlConnection = new-object system.data.SqlClient.SqlConnection($connectionString);
 
#Create a Dataset to hold the DataTable
$dataSet = new-object "System.Data.DataSet" "Dataset"
$query = "SET NOCOUNT ON;"
$query = $query + "SELECT [FileStoreFileId] FROM [dbo].[USR_DELETE_FILES];"
 
#Create a DataAdapter and populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $sqlConnection)
$dataAdapter.Fill($dataSet) | Out-Null
 
#Setup Kepion web service interface
$url = "http://localhost:8888/Services/Admin.svc?Wsdl"
$proxy = New-WebServiceProxy -uri $url -namespace WebServiceProxy -UseDefaultCredential
$proxy.Timeout = [System.Int32]::MaxValue
write-output "Open SQL connection.`r`n"
$sqlConnection.Open()
$dataTable = $dataSet.Tables[0]
$dataTable | FOREACH-OBJECT {
$progress = "Delete file with Id:" + $_.FileStoreFileId.toString()
write-output $progress
$proxy.FileStoreFileDelete("<KEPION_APPLICATION>","<FILE_STORE>", $_.FileStoreFileId)
$cmd = new-object System.Data.SQLClient.SQLCommand
  $cmd.CommandText = "DELETE FROM [dbo].[USR_DELETE_FILES] WHERE [FileStoreFileId] = @FileID"
  $cmd.Connection = $sqlConnection
  $cmd.Parameters.AddWithValue("@FileID", $_.FileStoreFileId) | Out-Null
  $cmd.ExecuteNonQuery() | Out-Null
}
write-output "Close SQL connection.`r`n"
$sqlConnection.Close()

 

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:

 

INSERT INTO [dbo].[USR_DELETE_FILES]
(FileStoreFileId) VALUES(1);
 
INSERT INTO [dbo].[USR_DELETE_FILES]
(FileStoreFileId) VALUES(2);

 

 

Setup SQL Agent

Step 1. Create a Credential for Kepion SI (Service Identity)

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

 

 

If you don't have one already, create a credential for your Kepion SI (Service Identity) by right-clicking on the Credentials folder and selecting New Credential. This identity will be used to connect into Kepion.

 

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

 

 

Step 2. Setup Proxy

 

EXEC [msdb].[dbo].sp_add_proxy
  @proxy_name=N'KepionSI',
  @credential_name=N'KepionSI',
  @enabled=1;
GO
 
-- Add proxy to PowerShell
EXEC [msdb].[dbo].sp_grant_proxy_to_subsystem
  @proxy_name=N'KepionSI',
  @subsystem_id=12;
GO

 

Step 3. Create a Job

 

-- Add Kepion category
IF NOT EXISTS (SELECT name FROM dbo.[syscategories] WHERE name=N'Kepion' AND category_class=1)
BEGIN
  EXEC [msdb].[dbo].sp_add_category
    @class=N'JOB',
    @type=N'LOCAL',
    @name=N'Kepion';
END;
 
-- Add job
EXEC [msdb].[dbo].sp_add_job
  @job_name=N'Delete Files',
  @enabled=1,
  @description=N'Remove files from a file store',
  @category_name=N'Kepion';

 

Step 4. Add Step to Job

Replace the <POWERSHELL_SCRIPT> with your tested script.

 

EXEC [msdb].[dbo].sp_add_jobstep
  @job_name=N'Delete Files',
  @step_name=N'Delete Files',
  @step_id=1,
  @subsystem=N'PowerShell',
  @command=N'<POWERSHELL_SCRIPT>',
  @database_name=N'master',
  @proxy_name=N'KepionSI';

 

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

 

EXEC [msdb].[dbo].sp_start_job N'Delete Files';