SQL Server Integration Services (SSIS) is a data integration tool used in companies both large and small. Graphical SSIS development tools reduce the need to learn a new coding language in order to extract, transform, and load your data. But organizations that use SSIS find that the lack of abstraction and inability to reuse code cause inconsistencies in package design as well as tedium and repetition for SSIS developers.
The Constant Struggle
As an example, imagine you have added a new project management application to your environment, and you must now add that application’s data to your data warehouse in order to report on project progress and costs. If you follow the traditional model of keeping a current copy of the source data in your data warehouse staging area and then transforming that data to populate facts and dimensions, SSIS developers now have the tedious job of manually creating the staging and dimensional model tables as well as all of the SSIS packages needed to populate them. While this job is repetitive, it still requires attention to detail. Developers must ensure they don’t omit any columns needed from the source data and that they add the appropriate audit columns when creating the tables. And they must make certain they are following the correct SSIS design pattern, which may include logging and error handling in addition to a specific way of handling data for each task (staging, populating dimensions, populating facts).
It’s not convenient to copy and paste SSIS tasks and components because they are so tightly coupled to the data they reference. Even good developers deviate from patterns while performing repetitive manual work simply due to human nature, forgetting an audit field here or violating a naming convention there.
Perhaps a developer devises a better method for error handling as the project starts. The new SSIS packages might employ this new error handling method, but it is difficult to justify the effort required to update all existing SSIS packages when there are outstanding tasks that provide more business value. So you accept that the packages are inconsistent and move on. Six months later, it is discovered that an edge case wasn’t covered in the new error handling method, so each package must be updated again. Team members get bogged down supporting this inconsistent solution instead of adding new data and functionality that advances the organization’s analytics capabilities.
A Robust Solution
This is a common scenario for which there is now a good solution. Business Intelligence Markup Language (Biml) is a programming language for creating business intelligence solutions in the Microsoft stack. It uses XML in combination with small nuggets of C# or VB code to automatically create SQL scripts, Integration Services packages, Analysis Services databases, and other objects. Varigence, the maker of Biml, offers free tools that create relational database objects and SSIS packages that are no different than the same objects created by hand.
Biml provides a way automate SSIS design patterns. This reduces the time required to complete a data integration project, and it helps employ consistent design patterns within and across projects. Re-generating multiple packages after making a change to a design pattern takes just a few minutes, so small changes to several similar packages are no longer a significant effort.
Automating SSIS design patterns allows teams to work more efficiently. Senior developers can stop solving the same problems over and over again. Instead, they can solve them once, automate the solution, and move on to new and interesting challenges. Junior developers still learn good development practices with Biml, but they require less supervision to create quality output in a shorter amount of time. SSIS developers that prefer typing code over the drag-and-drop interface of SQL Server Data Tools now get a better way to work in addition to the automation capabilities.
Consistent use of design patterns improves testing and support, too. The reuse of your code across projects exposes it to more scenarios and helps you identify issues sooner. While you should always test your SSIS packages, you can feel more confident about the tenth project in which you use a Biml pattern because the previous nine projects tested and improved upon it. Additionally, team members in a support role can more quickly diagnose issues because they know where to look in a consistently designed SSIS solution. DBAs can also appreciate the consistency of SSIS solutions as they collaborate with developers on security and performance tuning.
Automating SSIS development with reusable patterns and code can improve time to delivery, maintainability, and correctness of your data. This in turn allows your data integration team to complete more projects with high business impact. We would love to help you explore how using Biml to automate your SSIS development can benefit your organization. Contact us today to schedule a call with our team.
