Disclaimer
Your use of this download is governed by Stonebranch’s Terms of Use, which are available at https://www.stonebranch.com/integration-hub/Terms-and-Privacy/Terms-of-Use/
Overview
SQL Server Integration Services (SSIS) is a platform for building data integrations and data transformation solutions.
This Universal Extension task interactively allows users to list and select the SSIS Folder, Project, Environment Reference, and SSIS Package while creating the job. Furthermore, it can trigger the SSIS package execution in the Microsoft SQL server, monitor the SSIS Package execution, and fetch SSIS logs to the universal Controller when the SSIS package execution has completed.
Software Requirements
Software Requirements for Universal Agent
Universal Agent for Linux or Windows Version 7.0.0.0 or later is required.
Universal Agent needs to be installed with python option (--python yes).
Software Requirements Universal Controller
Universal Controller 7.0.0.0. or later is required.
Software Requirements for the Application to be Scheduled
Microsoft SQL Server Installed with Integration Services Catalog (SSISDB).
Deploy PYMSSQL python package to the universal agent where SSIS Task would be executed.
MS SQL Server Host name or IP address and the Integration services credentials.
Key Features
Dynamic Choice Fields to select the SSIS Folder/Project/Package/Environment Reference ID.
Launch SSIS Package execution.
Monitor SSIS Package execution.
Fetch SSIS Package execution logs.
SSIS Execution ID and Execution status captured for every execution in the Task Instance.
Connection to MS SQL Server is done via the Python PYMSSQL module.
Supports Windows and Linux Universal Agents in order to connect to the MSSQL server.
Import the SSIS Universal Extension Task Template
To use this downloadable Universal Template, you first must perform the following steps:
This Universal Task requires the /wiki/spaces/UC71x/pages/5178443 feature. Check that the Resolvable Credentials Permitted system property has been set to true.
Copy or Transfer the Universal Template file to a directory that can be accessed by the Universal Controller Tomcat user.
In the Universal Controller UI, select Configuration > Universal Templates to display the current list of Universal Templates.
Right-click any column header on the list to display an Action menu.
Select Import from the menu, enter the directory containing the Universal Template file(s) that you want to import, and click OK.
When the files have been imported successfully, the Universal Template will appear on the list.
Configure SQL Universal Tasks
For the new Universal Task type, create a new task and enter the task-specific Details that were created in the Universal Template.
Field Descriptions for SQL Universal Task
The following will provide a configuration Example for each of the supported Databases:
Database Type: MySQL
Database Type: Oracle - SQL
Database Type: Oracle - PLSQL Block
Database Type: PostgreSQL
Database Type: Microsoft SQL Server
Database Type: SAP HANA
Database Type: MySQL
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
ODBC Drivername | Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server. The following driver are available for selection:
Additional driver can be added in the SQL Task Universal Template under: Administration → Universal Templates → SQL → Fields → odbc_drivername → New |
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Get Output | prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run a SQL script on MySQL
Database Type: Oracle - SQL
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
Mode | Connection Authorization Mode: [sysdba | sysasm | sysoper | sysbkp | sysdgd | syskmt | None] The field is only relevant oracle connections |
Script Type | The field is only relevant for oracle connections [SQL Script | PL/SQL Block ]
|
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Get Output | Prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run a SQL Script on Oracle - SQL
Database Type: Oracle - PLSQL Block
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
Mode | Connection Authorization Mode: [sysdba | sysasm | sysoper | sysbkp | sysdgd | syskmt | None] The field is only relevant oracle connections |
Script Type | The field is only relevant for oracle connections [SQL Script | PL/SQL Block ]
|
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Get Output | prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run an Oracle - PLSQL Block
Database Type: PostgreSQL
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
ODBC Drivername | Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server. The following driver are available for selection:
Additional driver can be added in the SQL Task Universal Template under: Administration → Universal Templates → SQL → Fields → odbc_drivername → New |
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Get Output | prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run a SQL Script on PostgreSQL
Database Type: Microsoft SQL Server
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
Authentication | Supported Methods:
Note: If “Microsoft SQL Server - Windows Authentication” is chosen you should select under the Agent “Credentials” the Windows user, who should execute the script. The database credentials are not used for SQLSERVER -Windows Authentication. |
ODBC Drivername | Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server. The following driver are available for selection:
Additional driver can be added in the SQL Task Universal Template under: Administration → Universal Templates → SQL → Fields → odbc_drivername → New |
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Get Output | prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run a SQL Script on Microsoft SQL Server
Database Type: SAP HANA
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
Script | The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name. |
Column Separator | The field is only relevant for SAP HANA connections Output separator [ Semicolon | Comma | Hash | Whitespace ] |
Get Output | prints the SQL output to STDOUT In case of an Oracle Stored procedure print the dbms_output to STDOUT. |
Loglevel | Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL] |
Example: Run a SQL Script on SAP HANA
Document References
This document references the following documents:
Name | Location | Description |
|---|---|---|
Universal Templates | https://docs.stonebranch.com/confluence/display/UC71x/Universal+Templates | User documentation for creating Universal Templates in the Universal Controller user interface. |
Universal Tasks | https://docs.stonebranch.com/confluence/display/UC71x/Universal+Tasks | User documentation for creating Universal Tasks in the Universal Controller user interface. |