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.
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.
To add a user to the role, click on the ‘Add…’ button at the bottom-right of the properties pop-up window.
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.
Choose SSIS Package from the task type section.
Click on Next for the next page.
Choose a SQL Server Connection from the dropdown.
Only SSIS SQL Connections will be available.
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.
After you have clicked Select, the SSIS Package’s path will display below.
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’.
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.
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].
If an error occurs in SSIS it will also result in an error in Loome Integrate Online and show in the execution logs.