Steps

SQL SSIS Project

The SQL SSIS Project Step executes an SSIS project on a SQL SSIS server. Meaning that you can combine Ouvvi with your SSIS packages to do the following:

Schedule SSIS packages to run
Mix SSIS packages with other Ouvvi tasks (such as Data Sync Projects)
Document your Run Book process
Use Ouvvi User Configuration Variables with your SSIS packages
Run Packages directly from the Ouvvi Web Interface
Expose Packages for users to run manually without access to SQL Server
Collate SSIS Logs within Ouvvi real-time
Terminate SSIS Packages

Build your SSIS Packages and then deploy them to your SSIS Server Catalog as you normally would, you can then configure the Ouvvi step to run the package either on a schedule, via a trigger or manually.

Ouvvi uses the SSIS catalog stored procedures create_execution, start_execution, stop_execution and set_execution_parameter_value to run and manage the SSIS packages.

You only need a SQL connection to your SSIS Catalog from the Ouvvi Agent to run SSIS packages so Ouvvi and the Agent do not need to be installed on your SSIS server.

Note: Running SSIS packages remotely like this can cause an authentication double-hop issue where credentials are not correctly passed to a second server.

To get around this you can install an Agent on the SSIS server and configure the Step to run only on the SSIS server. Please see our Dependency Agent page for more information on how to set up Ouvvi Agents.

Configuration

SSIS

Parameter	Value
SSIS Connection String	The connection string to connect to your SSIS.
Folder Name	The name of the folder the project is stored under.
Package Name	The name of the SSIS package where the project is stored.
Project Name	The SSIS project name.
Environment ID	The ID of the SSIS environment to connect to.
Use 32Bit Runtime	Check the checkbox if you are required to run the package at 32bit runtime.

You can also add a list of input properties to the parameters table. By default the following property is added:

Type	Data Type	Name	Value
System	Int16	LOGGING_LEVEL	1

Your SSIS Connection String should point to your SSIS Server Database for example:

Data Source=SQL02;Initial Catalog=SSISDB;Integrated Security=True

Web API to Start SSIS

You can start SSIS packages via a Web API REST call when they are configured to run in Ouvvi. You can also pass configuration values via the CONTEXT value as part of the API call.

First set up the SSIS package as normal. In this example the source filename is going to come from the API call we set the value of SourceFileName to {{CONTEXT}}, which is the Ouvvi start context value passed on the URL.

Ouvvi SSIS Congifuration

API Call

Ouvvi exposes a URI which you can use to start an Ouvvi project. This URI returns a Json document detailing 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 that our SSIS project is going to import. e.g. http://demo.internal.simego.com/api/projects/start/20?context=C:\SSIS\Products.csv

You can call this from Windows PowerShell using the code:

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

You can query the status of the project execution by calling the project query URI endpoint using the instance ID value returned from the start call, using PowerShell like below:

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

Powershell Query API