SQL Server Integration Services

Loome Integrate Online supports SSIS. This task type is user friendly for existing SSIS packages.

Connecting to SSIS

To get started using SSIS in Loome Integrate Online, - You will need to grant the ssis_admin role to your Windows account that you use for SQL Server. - You will also need to create a SQL Server connection that points at the SSISDB database. When connecting to SSIS task types, use a standard SQL Server Connection that must point at the SSISDB in its connection string.

SSISDB.gif

Loome Integrate Online only supports the Project Deployment model for SSIS packages. Read more detail here.

How to add new users and check existing users of the ssis_admin role

In SQL Server Management Studio, open your SSISDB and click on Security, then expand Roles, followed with Database Roles.

Right-click on ssis_admin and select Properties.

SSIS Admin role

To add a user to the role, click on the ‘Add…’ button at the bottom-right of the properties pop-up window.

Add a user to the role

Create a SSIS Package Task

Create a New Task.

Choose a unique name to identify this task, and then choose an agent from the dropdown that will run this task.

SSIS task name and agent

Choose SSIS Package from the task type section.

SSIS task type

Click on Next for the next page.

Choose a SQL Server Connection from the dropdown.

Only SSIS SQL Connections will be available.

SSIS SQL Connection dropdown

Adding a SSIS package is like any other task in Loome Integrate Online, where you can use the package selector to easily find available packages to run. You then need to provide the package path that can be selected from the dropdown menu. Once you click Select the SSIS Package’s path will display below.

Choose a package from the Package Selector dropdown. The package path can be selected from this dropdown.

Click on Select beside this field after you have chosen a package.

Package selector dropdown

After you have clicked Select, the SSIS Package’s path will display below.

Package path field

Optionally, you can manually provide the package path in the field SSIS Package Path.

SSIS Packages are provided in the format of ‘Folder/Project/Package’.

Select SSIS Package

You can then add any additional parameters. Loome Integrate online supports all SSIS parameter types.

You can provide a parameter name and an optional default value in the task. Click on ‘+’ to add the parameter.

Additional Parameters

Click Submit and you can then run the task and view its execution logs.

Or you can click on Edit Parameter Values on the job page, and provide a value to the parameter you added in the task.

Viewing SSIS Logs

Click Run Job and it will then showcase the SSIS logs in the execution history. The logs will appear on the Executions page of a job and can be identified with the prefix of [SSIS].

All SSIS logs will be prefixed with [SSIS].

Execution Log

If an error occurs in SSIS it will also result in an error in Loome Integrate Online and show in the execution logs.