Web API to Start SSIS Package

Using Web API/REST to Start and Monitor Remote SSIS Packages

12 April 2018

This is an example of how you can host your SSIS packages in Ouvvi and use the Ouvvi Web API to start the SSIS package on a remote SSIS Server including passing some configuration value on the URL.

In this example we have a SSIS project that imports a CSV file into SQL Server and we are going to enable calling this via a Web API call and pass the name of the CSV file to import.

Configuration

First we setup the SSIS Package in Ouvvi and since the source filename is going to come from the API call we set the value of the SourceFileName to {{CONTEXT}} which is the Ouvvi Start Context value passed on the URL.

Ouvvi SSIS Configuration

API Call

Ouvvi exposes a URI you can use to Start an Ouvvi project this returns a Json document detaiing the start and instance id, you can then use the instance id to get the status of the project.

Powershell Start Project

Start the project by calling the Start Project URI endpoint, setting the context to the filename our SSIS project is going to import. In this example our URI is http://demo.internal.simego.com/api/projects/start/20?context=C:\SSIS\Products.csv and below is an example calling this API using Windows PowerShell.

Invoke-RestMethod -Method GET -ContentType application/json -UseDefaultCredentials -Uri http://demo.internal.simego.com/api/projects/start/20?context=C:\SSIS\Products.csv

PowerShell Start API

Powershell Query Instance ID

Query the Status of the project execution by calling the Project Query URI endpoint using the Instance ID value returned from the start call.

Invoke-RestMethod -Method GET -ContentType application/json -UseDefaultCredentials -Uri http://demo.internal.simego.com/api/projects/query/40749

PowerShell Query API

Questions?

If have any questions then send us a message to support@simego.com. We love to hear feedback from our customers and will keep striving to add more functionality to our software.

| |