Release Config with SSIS package - OptimalBI/optimal-data-engine-mssql GitHub Wiki

ODE configuration release mechanism includes a step of writing into the file. We found that some organisations limit developer security permissions, making this function unavailable for them. We have implemented this SSIS package to be used in this case. Configuration data will be copied into the target environment to the ODE Configuration database directly without writing on disk.

SSIS release management package can help with both partial and full releases. Package has a range of execution parameters, so release process could be customised.

You can download the SSIS package from GitHub using this link.

Install this project into the SSIS catalog on the target release environment. E.g. if you want to promote ODE configuration from DEV to TEST server, install release package on the TEST server. Also installation require SQL server login "ODERelease" to be created on the source environment. Grant this login read and write access to the ODE Config database, because release SSIS package could build (compile) release script and write it into the table.

You can create the following SSIS package environments which will store the usual types of release, Partial and Full release. Once these environments are created, SSIS release project could be configured to use them. You will be able just to pick up an environment for the typical release scenario.

Full release environment:

Partial release environment:

To configure SSIS project to use these environments, right click on the Release project, navigate to the Reference tab. On this screen, add references to both environments. On the parameters tab hook up environment parameters. To do that, click on "..." button on the right hand side of the parameter line, choose "Use environment variable" and choose the variable from the drop-down list.

Right click on the package in the SQL Server Integration services catalog, choose "Execute". Select the environment from the drop-down list. This will populate most of the parameters with the predefined values. Provide the release number and release description in case if you are inserting the new header; or just the release number in case if the header exists already. Once execution has started, you can start monitoring the load via the execution report. Package execution shouldn't take long. Refresh the report if execution is still running. Once execution has succeeded, configuration is released to the target server.

In case of failure, follow the links on the execution report to open "All messages" report for the error message.

This package only copies configuration. As a precaution measure, this package doesn't apply new configuration to Data Vaults automatically. Run this script to apply configuration and create new DV structures. If you use staging stored procedures for ETL logic, they should be released to the object server as well.