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:
- Create a SQL Agent Job.
- Add PowerShell script to a SQL Agent Job.
-
Configure PowerShell script to perform the following:
- Read data from a SQL table.
- Iterate over table result and call the Kepion Web Service.
- Write results back to a SQL table.
Before You Start
For this example, you will need to identify the following:
- <KEPION_APPLICATION>: An application with the File Store functionality implemented.
- <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';
Comments
0 comments
Please sign in to leave a comment.