In real world environments changing source systems can easily break the whole Business Intelligence System.
Using native Database Tools like Microsoft SQL Server Integration Services give you a lot of flexibility to build the Data Integration Solution you need. Just identifying where you used a field that no longer exists can be a huge pain and literally take days in complex systems.
How Halo Source handles Source System Changes
Halo Sources stores Information about the Source Metadata in each Project. This way you know exactly which fields are used in which ETL Package or transformaton.
To check if your Data Integration is in Sync with the Source System you can simply Refresh the Source Metadata.
All Items that are no longer available or need your attention are color coded with a red dot.
You can open the Items and choose how to fix the Issue. You can use Default values, SQL Expressions, link to other fields.
With this Technology identifying issues from changed Source Systems becomes very easy and straightforward.
If you do not use Halo Source you have to identify the changed or missing fields manually.
To achieve this you need to
- manually gather information about what has changed
- open all SSIS Packages one by one in Microsoft BI Develoment Studio
- Check every Dataflow if you are using a Table that has changed
- if a table has changed you need to update the strucutre manually in your Staging area
- then you need to go through all steps of the data flow and add/change the field in the data flow
- then you need to double check if the change broke another package
Real world projects often consists of several some 50 Integration Services packages loading several hundred tables. To work through this project and make the proper changes can take several weeks of work. Especially if the documentation is not up to date.
In a Source Project with some 50 ETL Packages and several hundrred tables you can identify the whole impact of all source System changes in a few minutes.
All you need to do is to refresh metadata and ALL required changes are marked with a red dot.
The effort to fix these depends on how you get the correct values. In most cases it takes less than 5 minutes to fix a single issue.