The Ultimate Guide to SQL Server Data Quality
We can all pretty much agree that there are pros and cons to
integrating data quality from shared procedures. SQL Server technology offers
various options when it comes to integrating third party solutions. Knowing the
right route to take can be determined by preference in architecture. Here are a
couple of the most commonly used ways to integrate Melissa Data’s flagship Data
Quality Solutions within SQL Server:
1. Transact-SQL OLE Automation Objects
Melissa Data supports the integration of OLE
Automation Objects for the sake of legacy versions of SQL Server while also
enabling the registering of COM Object library interfaces with sp_OACreate. Due
to the in-process constraints this method contains, it is one we least advocate
2. Extended Stored Procedures
This is the most common ways users call the
Melissa Data API’s. It allows for accessing third party DLLs and offers a quick
and easy way to connect to SQL Databases to iterate record through Data
Cleansing regimens. Wrapper functions are automatically generate allowing for
direct access to APIs.
3. Common Language Runtime (CLR) Integration
This is the least common integration method.
However, it is not the least effective. This integration allows for calling
.NET Code from SQL Server to perform transformations for your data. It results
in performing tasks that may have been previously impossible due to limitations
of queries. Melissa Data’s APIs can be called through a .NET project in your
CRL Project as opposed to calling it through an Extended Stored Procedure. This
offers a more stable platform when working with DLLs and we highly recommend
4. SQL Server Integration Services
Another common implementation method is
SSIS-an ETL/Data Integration Platform Bundles with SQL Server. We provide
custom GUI-based components for SSIS that are accessible through the Data Flow
Task. Our integration with SSIS is seamless and easy to use as it makes an
interface and does not require any type of coding. We support both 2008 and
2012 versions of SQL Server.
5. SQL Server Data Quality Services (DQS)
DQS is a data quality solution developed by
Microsoft and offered in the Microsoft Azure Marketplace. It offers third party
reference services which integrate with Melissa Data’s flagship suite of Data
Cleansing Solutions. Melissa Data’s solutions in the Microsoft Azure
Marketplace can be consumed within DQS Platform.
For more information, or to learn about SQL Server data quality, visit our website!
By Natalia Crawford