Microsoft SQL: SSIS Package Execution

Owned by Former user (Deleted)
Last updated: May 17, 2023 by Stonebranch
4 min read

Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at  Stonebranch Integration Hub - 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 Universal Controller when the SSIS package execution has been completed.

Version Information

Template NameExtension NameExtension Version
MSSQL-SSIS-ExecutionMS-SSIS-PACK-Execution1.0.4

Software Requirements

Software Requirements for Universal Agent

  • Universal Agent for Linux or Windows Version 7.1.0.0 or later is required.

  • Universal Agent needs to be installed with python option (--python yes).

Software Requirements Universal Controller

  • Universal Controller 7.1.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 Hostname 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 are 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 Universal Template

To use the Universal Template, you first must perform the following steps.

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.

  2. To import the Universal Template into your Controller, follow these instructions.

  3. When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear on the list.


Configure Universal Tasks

For 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 Package Execution Universal 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 Package Execution Universal Extension Tasks

SSIS Universal Task - Select DB Name

Select SSIS Folder Name

Select SSIS Project Name

Select SSIS Package

SSIS Execution - Task Instance

SSIS Execution Logs STDERR / STDOUT

Document References

This document references the following documents:

Name

Description

Python PYMSSQL Module Reference

Documentation about the PYMSSQL Module used in this Universal Task.

Changelog

ue-microsoft-sql-server-ssis-1.0.4 (2023-05-17)

Bug fix

  • Fixed: Environment Reference ID is optional and when there is no value, the extension fails.

ue-microsoft-sql-server-ssis-1.0.3 (2023-04-24)

Bug fix

  • Fixed: Task Doesn't fail when SSIS project name contains spaces