Integrate SQL Agent Job and Powershell

Author: Kepion Product Team

In this article, we'll explain how to integrate Kepion with a SQL Agent Job and a PowerShell script. You should consider integration if you want to automate a file cleanup for a given File Store.

 

Here is the general integration process:

 

1. Create a SQL Agent Job.

2. Add PowerShell script to a SQL Agent Job.

   i. Read data from a SQL table.

   ii. Iterate over table result and call the Kepion Web Service.

   iii. Write results back to a SQL table.

 

 

Before You Start

For our 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

1. Open SQL Server Management Studio (SSMS) and create the following table in the Application database:

 

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

 

Tip: If you are unsure of how to create a table, refer to this article. 

 

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

 

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

 

3. Insert the IDs for the identified files:

 

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

 

Note: If you use invalid IDs, no files will be affected.

 

 

Setup PowerShell

1. Replace <KEPION_APPLICATION>, <FILE_STORE>, and the 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()

 

2. 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);

 

 

Set up SQL Agent

1. Open SSMS and connect to your SQL Server instance.

2. Right-click Credentials and select New Credential...

 

 

3. Configure the credential. This identity will be used to connect to 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).

 

4. Set up Proxy using the following query:

 

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

 

5. Create a Job using the following query:

 

-- 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';

 

6. Add Step to Job by running:

 

Note: Replace  <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';