...
This Universal Task requires the /wiki/spaces/UC71x/pages/5178443 feature. Check that the Resolvable Credentials Permitted /wiki/spaces/UC71x/pages/5177877 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 /wiki/spaces/UC71x/pages/5178054.
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. Refresh your Navigation Tree to see these tasks in the Automation Center Menu.
Configure
...
SSIS Universal Extension Tasks
For the new this Universal Extension Task type, create a new task and enter the task-specific Details that were created in the Universal Template.
Field Descriptions for
...
SSIS Universal
...
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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
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:
MySQL: MySQL ODBC 8.0 Unicode Driver
MS SQL Server: SQL Server Native Client 11.0,
MS SQL Server: ODBC Driver 17 for SQL Server
PostgreSQL: PostgreSQL Unicode
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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
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 ]
SQL Script - select to execute an SQL Script
PL/SQL Block - select to run an 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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
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 ]
SQL Script - select to execute an SQL Script
PL/SQL Block - select to run an 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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
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:
MySQL: MySQL ODBC 8.0 Unicode Driver
MS SQL Server: SQL Server Native Client 11.0,
MS SQL Server: ODBC Driver 17 for SQL Server
PostgreSQL: PostgreSQL Unicode
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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
Database Credentials
...
Database Connection Credentials
...
Authentication
...
Supported Methods:
Microsoft SQL Server - Windows Authentication
Microsoft SQL Server - SQL Server Authentication
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:
MySQL: MySQL ODBC 8.0 Unicode Driver
MS SQL Server: SQL Server Native Client 11.0,
MS SQL Server: ODBC Driver 17 for SQL Server
PostgreSQL: PostgreSQL Unicode
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
...
Database Type
...
Type of database to connect:
MySQL
Oracle
PostgreSQL
Microsoft SQL Server
SAP HANA
...
Database Name
...
Database name or oracle service name
...
Database Server
...
Database Servername e.g. localhost
...
Database Port
...
Port of the Database e.g.
MySQL: 3306
Oracle: 1541
PostgreSQL: 5432
Microsoft SQL Server: 1433
SAP HANA: 39013
...
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
...
Extension Task
Field | Description |
MSSQL Server Name | IP address or Host name of the MSSQL Server. |
MSSQL DB Credentials | MS SQL server credentials for accessing the Integration service Catalogs and SSISDB privileges to create temporary stored procedures. |
MSSQL Database Name | Dynamic choice field to select the database , Default value would be SSISDB. |
SSIS Folder Name | Dynamic choice field to select the SSIS Folder. |
SSIS Project Name | Dynamic choice field to select the Project Name. |
SSIS Package Name | Dynamic choice field to select the SSIS Package. |
Environment Reference ID | Dynamic choice field to select the associated Environment Reference ID for the selected SSIS Package. |
Fetch SSIS Package Logs | If True, then the SSIS execution logs would be fetched to Universal Controller (STDOUT). |
SSIS Execution ID | Output-only text field visible only in task instances; provides the SSIS Execution ID. |
SSIS Execution Status | Output-only text field visible only in task instances; provides the SSIS Execution status from MSSQL server. |
Examples of SSIS Universal Extension Tasks
SSIS Universal Task - Select DB Name
Select SSIS Folder Name
Select SSIS Project Name
