In this article, we will guide you through the steps to create and schedule the automated process of deploying an application, syncing up AD users, and managing workflow plans.
1. Create PowerShell Script
Create a new file called Task.ps1 and save it to a location that SQL Server Agent can access. This automation file will contain PowerShell commands that can communicate with Kepion Server.
Replace the following placeholders with appropriate values:
|<KEPION_SERVER_NAME>||The name of the web server for Kepion Planning.|
|<APPLICATION_NAME>||The name of the Kepion application/database to automate.|
|<APP_NAME>||The name of the app. This value can be found in the AuthorizationName column of the [dbo].[Authorizations] table.|
## Web Service Definition
$url = "http://<KEPION_SERVER_NAME>:8888/Services/Admin.svc?wsdl" ## Setup Proxy
$proxy = New-WebServiceProxy -uri $url -namespace WebServiceProxy -UseDefaultCredential
$proxy.Timeout = [System.Int32]::MaxValue ## Call Web Service
## Same as Deploy to OLAP from UI (Relational, OLAP, Process step from UI)
Write-Output "UpdateOlapDatabase '<APPLICATION_NAME>'"
## Same as AD Sync + Save (Security step from UI, plus AD Sync)
Write-Output "UpdateOlapSecurity '<APPLICATION_NAME>'"
## System wide AD Sync
## Purge Plan – Clears all saved plans
Write-Output "ClearPlan '<APPLICATION_NAME>' '<APP_NAME>'"
## Reset Plan – Updates forms in saved plans with latest definition and variables
Write-Output "ResetPlan '<APPLICATION_NAME>' '<APP_NAME>'"
Open SQL Server Management Studio (SSMS)
To open SSMS from Windows, navigate to the Search Box and enter SQL Server Management Studio. Click the program that comes up.
2. Connect to SQL Server Agent
SQL Server Agent can be found under the server name. Ensure that SQL Server Agent is started and if not right-click and start.
3. Verify Domain Account
Ensure you have a domain account that can be used to connect to Kepion Planning for automation. This account will need to be assigned with both Modeler and Administrator privileges or be System Administrator within Kepion.
4. Create Credential
Under Security, setup Credentials for use by the automation task. Right click Credentials and select New Credentials….
5. Configure Credentials
Enter the Domain Account from the step Verify Domain Account in Identity and the Password. Click OK.
6. Create Proxy
Right click Proxies and select New Proxy….
7. Configure Proxy
Provide the Proxy a name such as Kepion Automation Proxy. Select the Credential from step Create Credential and grant access to PowerShell. Click OK.
8. Create Job
Right-click on Jobs and select New Job….
Give the job a name such as Automate Kepion Task. Click on Steps.
Click on New… to define a new step. The step will contain an “action” that this job will run.
Define Job Step
Provide a Step name, such as Call Kepion Web Service.
Select the Type as PowerShell and select the Run as: with the proxy that was created from Configure Proxy.
Copy the script from the step Create PowerShell Script and paste it to the Command window. Or click Open to locate the Task.ps1, which will copy the script content from the file to the Command window automatically.
Create a schedule for the job to run by clicking on New….
You can configure the job to run on any schedule. In the example below, we are creating a weekly schedule to run on every Sunday at 12 AM.
9. Test Job
Right click on the newly created job and run. You should be able to see a Success message like the one below. If not, please check the error message, fix the issue and test again.