Build Faster Run Smarter

Peter: AnalyticsCreator is a data warehouse automation application. It extracts metadata from source systems and uses it to generate data warehouse design and development artefacts.

DILM Suite supports lifecycle management. It helps organise deployment, comparison, and recovery across environments.

AnalyticsCreator is used primarily at design time, while DILM Suite supports the operational lifecycle. Together, they provide a practical approach to metadata-driven development and governed deployment.

Andy: DILM Suite is a collection of tools designed to support governance. Governance matters because it increases confidence in deployments and helps organisations meet regulatory requirements.

Today, I will focus on three tools: DILM Deployment Utility, SSIS Catalog Compare, and SSIS Framework Manager.

DILM Deployment Utility helps promote SSIS artefacts between environments. SSIS Catalog Compare helps compare catalog contents and identify differences. SSIS Framework Manager supports orchestration, which is another important part of governed execution.

Gustavo: I will start by opening AnalyticsCreator and creating a new data warehouse called “New Northwind DWH”.

AnalyticsCreator creates a metadata repository for the project in a SQL Server database. This repository stores the metadata from the data sources, business rules, layers, and design objects.

Next, I add Northwind as a data source using a connector and connection string. Once the connection is available, I can use the metadata to generate the data warehouse structure.

Gustavo: I now use the Data Warehouse Wizard to create a classic Kimball-style data warehouse.

I select the tables that should be included and mark Order Details as a fact table. I also configure naming and package options, include a calendar, and apply default transformations.

AnalyticsCreator then generates the data warehouse structure from metadata. This gives us the required layers, transformations, and fact structure without manually building each object.

Gustavo: Next, I create a deployment package called “New Northwind Data Warehouse”.

I configure the DACPAC deployment and generate the SSIS packages. The workflow package coordinates execution, so the generated packages run in the correct order.

This approach works for a small demo model, but it can also support much larger projects with many tables and packages.

Andy: I now open the generated Visual Studio project from the AnalyticsCreator repository folder.

Manual editing is rarely needed because most changes can be made in AnalyticsCreator and regenerated from the metadata model. However, the generated Visual Studio project is still available when teams need to inspect or work with the SSIS artefacts directly.

I deploy the SSIS project to a development SSIS catalog folder called “NWDW”.

Andy: I open SSIS Catalog Compare and connect to both the development and production environments.

When I compare the catalogs, I can see that the newly deployed folder exists in development but not in production. Expanding the differences shows the new SSIS catalog contents that are ready to be promoted.

This gives us a clear view of what has changed before anything is deployed to production.

Andy: I generate the catalog scripts and open the generated catpack file in DILM Deployment Utility.

From there, I select the relevant folder and deploy it to the production SSIS catalog. The utility handles dependencies in the correct order. For example, it creates the folder before deploying the project.

This gives us a controlled way to promote SSIS artefacts from one lifecycle tier to another.

Andy: After deployment, I refresh the target environment and confirm that the NWDW folder now exists in production.

I compare development and production again, and the settings match. This gives us confidence that the work created and tested in development has been promoted correctly to production.

In real projects, organisations may use more than two environments. The same approach can support additional lifecycle tiers, depending on governance requirements.

Gustavo: I now return to AnalyticsCreator and add the Orders and Region tables.

This change is made in metadata, so production is not changed directly. I also isolate the new Orders table into a separate package, which keeps the change easier to test and manage.

After correcting a mistake, I redeploy the updated model. Because the model is metadata-driven, I can make the correction and regenerate the artefacts without manually reworking the SSIS packages.

Andy: This is where automation changes the troubleshooting process.

Instead of manually fixing generated packages, we can correct the metadata model and regenerate the project. That gives us repeatability. The same process runs the same way each time, which reduces manual effort and improves consistency.

When something goes wrong, the better approach is to look at the model and the process, correct the source of the issue, and regenerate the artefacts.

Andy: I deploy the updated Visual Studio project to the development SSIS catalog.

Then I use SSIS Catalog Compare to identify the differences between development and production. The comparison shows that some packages now have different versions and are ready to be promoted.

This makes it easier to see exactly what changed and decide what should move forward.

Andy: I use the “Select mismatched” option to choose the changed packages for deployment.

A workflow package error appears, so I remove the problematic package from the promotion. That allows me to deploy the remaining packages without blocking the whole process.

Gustavo points out that the issue was identified quickly without checking commits, tasks, or unrelated changes. That is an important benefit: the tools show the difference clearly and allow selective promotion.

This supports controlled delivery. We can promote the packages that are ready and hold back anything that needs further review.

Andy: Ongoing changes require repeated testing and deployment. Every model change can introduce data quality issues, so it is not enough to confirm that a package runs successfully. The resulting data also needs to be checked.

This is where continuous improvement, continuous integration, and continuous deployment come together in data engineering. Teams can update the model, regenerate the artefacts, test the output, compare environments, and promote changes in a controlled way.

Richard: Before we close, we have upcoming workshops on change management and lifecycle management for data warehouses and SSIS packages. Participants can test AnalyticsCreator and DILM Suite on their own data, with guidance from Andy and Gustavo.

Thank you, everyone, for joining.