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.
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
| Parameter | Description |
|---|---|
| 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, so we set the value of SourceFileName to {{CONTEXT}}, which is the Ouvvi start context value passed on the URL.
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 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
