Troubleshooting DQS Cleansing Failures in SSIS Packages on SQL Server

Table of Contents

Data integration processes often involve ensuring the quality and consistency of data. Microsoft SQL Server Integration Services (SSIS) is a powerful platform for building enterprise-level data transformation and extraction solutions. One key component for data quality is the Data Quality Services (DQS), which allows users to perform data cleansing and matching tasks. When integrating DQS cleansing into SSIS packages, developers might encounter specific issues, particularly when deploying these packages across different server environments.

This article focuses on a common problem faced by developers using the DQS Cleansing component in SSIS within a SQL Server environment. It details the symptoms, the underlying cause, and provides a practical workaround to resolve failures related to the Data Quality Knowledge Base (KB) when packages are moved between servers. Understanding this issue is crucial for successful SSIS development and deployment involving DQS.

Symptoms¶

Consider a typical development and deployment workflow involving SSIS and DQS. Developers build SSIS packages that leverage the DQS Cleansing component to process and clean data flowing through the package. This development usually occurs on a specific SQL Server instance where DQS is installed and configured, and where relevant Data Quality Knowledge Bases are created and published.

A common practice involves moving these developed SSIS packages from one server environment to another. This might be from a development server to a testing server, or from testing to a production server. After the package is moved, it’s necessary to update the connection managers within the SSIS package to point to the SQL Server instance hosting DQS in the new environment. This is a standard step in the deployment process to ensure the package connects to the correct resources.

Despite updating the DQS Cleansing connection manager to the new server name, executing the SSIS package in the target environment results in an error. This error specifically points to a failure within the Data Flow Task where the DQS Cleansing component is used. The package execution terminates, preventing the data cleansing process from completing successfully. The error message indicates an issue with locating the required Knowledge Base, suggesting that the link between the SSIS component and the DQS KB is broken in the new environment.

Data Flow Task Name:Error: Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: The Knowledge Base does not exist [Id : 1000999].
at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec()
at Microsoft.Ssdqs.Proxy.EntryPoint.KnowledgebaseManagementEntryPointClient.DQProjectGetById(Int64 id)
at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.PostExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)
Data Flow Task Name:Error: System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessChunk(ReadOnlyCollection`1 fieldMappings, ReadOnlyCollection`1 records, CorrectedRecordsStatusStatistics& correctedRecordsTotalStatusStatistics)
at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

The error message explicitly mentions that a Knowledge Base with a specific ID (e.g., 1000999) “does not exist.” This error is followed by a System.NullReferenceException, which is a consequence of the DQS component failing to initialize correctly because it couldn’t find the necessary KB. The presence of the KB ID within the error message is a crucial clue for diagnosing the problem.

Cause¶

The root cause of this problem lies in how the SSIS DQS Cleansing component references the Data Quality Knowledge Base. While you update the DQS connection manager to point to the new server instance, the DQS Cleansing component itself inside the Data Flow Task stores a reference to the specific internal ID of the Knowledge Base it was configured to use on the original development server. This internal ID is an integer value assigned by the DQS instance.

When a DQS Knowledge Base is created and published on a specific SQL Server instance running DQS, it is assigned a unique numerical identifier by that instance. This ID is distinct and internal to that particular DQS installation. Each time you modify and re-publish a Knowledge Base, a new version might be created, or in some scenarios, the internal ID could change or the association might require refreshing. More importantly, if you create or import a Knowledge Base with the same name on a different DQS instance (e.g., on your production server), that instance will assign it a new and different internal numerical ID.

The SSIS package’s DQS Cleansing component, when configured in the SQL Server Data Tools (SSDT) designer, doesn’t just store the name of the Knowledge Base; it persists the internal ID of the KB it was configured against at the time of design. This is visible if you inspect the package’s DTSX XML code. For example, a property within the DQS Cleansing component definition might look like this:

<property dataType="System.Int64" name="KnowledgebaseName" typeConverter="NOTBROWSABLE">1000999
</property>

Notice that the name property is “KnowledgebaseName”, but the dataType is System.Int64, and the value is 1000999, representing the internal numeric ID, not the actual name of the KB (like “Customer Data Cleansing”). This indicates that the component relies on this specific ID to locate the KB on the connected DQS server.

When you deploy the SSIS package to a new server environment and update the DQS connection manager to point to the DQS instance on that server, the DQS Cleansing component still tries to find a Knowledge Base with the original persisted ID (e.g., 1000999) on the new DQS instance. However, unless by sheer coincidence (which is highly unlikely), the Knowledge Base you intend to use on the new server will have been assigned a different internal ID by that DQS instance, even if it has the same name and structure as the original KB.

The error message The Knowledge Base does not exist [Id : 1000999] confirms this. The DQS Cleansing component connected to the target DQS server, requested KB 1000999, and the target server reported that no KB with that specific ID exists in its catalog. The subsequent NullReferenceException occurs because the component cannot proceed without a valid Knowledge Base reference.

To verify this cause, you can connect to the SQL Server instance hosting the DQS databases (DQS_MAIN) on the target server where the SSIS package is failing. Run the following SQL query, replacing 1000999 with the ID found in your error message:

SELECT id, Name, Description, CreateDate, CreatedById FROM [DQS_MAIN].[dbo].[A_KNOWLEDGEBASE] WHERE id = 1000999;

If this query returns no rows, it confirms that a Knowledge Base with that specific internal ID does not exist on this DQS instance, validating the cause of the failure. You could also run the query without the WHERE clause to list all KBs and their IDs on the target server and find the ID of the KB you intended to use, confirming it’s different from the ID causing the error.

Understanding the DQS Cleansing Component in SSIS¶

The DQS Cleansing component is a transformation within the SSIS Data Flow Task. Its purpose is to connect to a DQS Server and perform data cleansing based on rules defined in a selected Knowledge Base. When you add this component to a Data Flow, you configure it by:

1. Selecting a DQS Connection Manager that points to the desired SQL Server instance running DQS.
2. Specifying the Data Quality Knowledge Base to use for cleansing. This is typically chosen from a dropdown list populated by the selected DQS Server.
3. Mapping the input columns from your data flow to the relevant domains within the chosen Knowledge Base.

It is during step 2, selecting the Knowledge Base in the designer, that the component internally records the KB’s unique internal ID from the currently connected DQS server. This ID is then saved as part of the SSIS package definition. The issue arises because this ID is environment-specific.

Let’s visualize the interaction:

```mermaid
graph TD
A[SSIS Data Flow Task] → B(DQS Cleansing Component);
B → C{DQS Connection Manager};
C → D[SQL Server Instance with DQS];
D → E[DQS_MAIN Database];
E → F[A_KNOWLEDGEBASE Table];
B – Requests KB by ID → F;
F – Returns KB Definition (if ID exists) → B;
B – Performs Cleansing → A;

%% Problem Path
D_old[Original SQL Server Instance with DQS] --> E_old[DQS_MAIN Database (Original)];
E_old --> F_old[A_KNOWLEDGEBASE Table (Original)];
B -- Configured with KB ID from --> F_old;
C -- Updated to point to --> D; %% New server
B -- Still requests KB by OLD ID from --> F; %% New server's table
F -- KB ID not found --> B;
B -- Failure --> A;

```
Diagram illustrating the SSIS-DQS interaction and the failure point.

The diagram shows how the DQS Cleansing component relies on the connection manager to reach the DQS server, but its internal configuration holds onto an ID obtained during the initial setup phase from the original server. When the connection manager is redirected to a new server, the component still uses the old ID, leading the new server to report that the KB with that specific ID is unknown.

Understanding this reliance on the internal ID is key to implementing the correct workaround and adopting better deployment practices. Simply changing the server name in the connection manager is insufficient because the component’s specific KB reference, encoded by the old server’s ID, remains unchanged in the package definition.

Workaround¶

The most straightforward way to resolve this issue after deploying an SSIS package containing a DQS Cleansing component to a new environment is to refresh the component’s configuration within the SSIS designer. This process forces the component to re-select the intended Knowledge Base from the DQS instance it is now connected to, thereby updating its internal reference to the correct ID for the new environment.

Here are the steps to apply the workaround:

1. Change the DQS server name or deploy the SSIS package: First, ensure the SSIS package has been deployed to the target environment and the DQS Connection Manager within the package is configured to point to the correct SQL Server instance hosting DQS in this new environment. This is a prerequisite as the following steps require the package to be able to connect to the target DQS server.
2. Open the Integration Services Package in SQL Server Data Tools (SSDT): Open the failing SSIS package (.dtsx file) in your SSIS development environment (SSDT or Visual Studio with SSIS projects). This allows you to edit the package design.
3. Locate the affected Data Flow Task: Navigate to the Data Flow Task within the Control Flow that contains the DQS Cleansing component experiencing the error.
4. Double-click the DQS Cleansing component: Open the custom editor for the DQS Cleansing transformation component within the Data Flow. This editor is where you configure the component’s properties, including the Knowledge Base selection and column mappings.
5. Update the KB listed in the ‘Data Quality Knowledge Base’ drop-down list: Inside the DQS Cleansing component editor, you will see a dropdown list labeled “Data Quality Knowledge Base”. Even if the name of the correct Knowledge Base appears selected (because the SSIS package remembers the name), you must re-select it or select it explicitly from the dropdown. This action prompts the component to query the currently connected DQS server (as defined by the DQS Connection Manager) for its list of available Knowledge Bases and, crucially, to capture the correct internal ID for the selected KB on this server.
6. Review Column Mappings: After re-selecting the Knowledge Base, quickly review the column mappings tab within the component editor to ensure your input columns are still correctly mapped to the domains in the selected Knowledge Base. While this usually persists correctly, it’s a good practice to verify.
7. Click OK, and then save the package: Close the DQS Cleansing component editor by clicking OK, and then save the changes to the SSIS package file (.dtsx).

After performing these steps, the SSIS package file will contain the correct internal ID for the intended Knowledge Base as it exists on the target DQS server. When you execute this modified package in the target environment, the DQS Cleansing component will now correctly identify and load the Knowledge Base, resolving the “Knowledge Base does not exist [Id: …]” error.

This workaround effectively involves re-pointing the DQS Cleansing component’s internal reference to the specific KB on the target DQS server, capturing its unique ID in that environment.

Preventing Future Occurrences¶

While the workaround fixes the issue after deployment, it’s better to adopt strategies to prevent this problem from occurring in the first place, or at least minimize the need for manual intervention after each deployment. The core of prevention lies in managing the DQS environment and SSIS deployment process more carefully.

• Consistent DQS Environments: The ideal scenario is to have consistent Data Quality Knowledge Bases across your development, testing, and production DQS environments. This means exporting KBs from the development environment and importing them into test and production. When importing, DQS assigns a new internal ID. Therefore, importing alone doesn’t solve the ID problem, but it ensures the KB structure and rules are identical.
• Post-Deployment Configuration: Recognize that updating the DQS component’s KB reference is a necessary step after deploying an SSIS package containing DQS Cleansing to a new DQS instance. Plan for this as part of your deployment process. This could involve having a step where the deployed package is briefly opened in SSDT (or programmatically modified, though this is more complex) to perform the KB re-selection.
• Environment-Aware Packages (Advanced): For highly automated deployments, consider if it’s possible to make the KB selection more dynamic. SSIS package configurations or parameters can be used to control connection strings and other properties. However, directly parameterizing the internal KB ID required by the DQS Cleansing component is not straightforward due to its internal property type (System.Int64 named KnowledgebaseName). A more advanced approach might involve a pre-execution script task that queries the target DQS server’s DQS_MAIN database to find the ID of the KB by name and then programmatically updates the DQS Cleansing component’s relevant property before the Data Flow Task executes. This requires significant custom scripting and understanding of the SSIS object model.
• Standardized Deployment Procedures: Document the process of deploying SSIS packages with DQS Cleansing components. Train your deployment team or release managers on the specific steps required to update the DQS component configuration in SSDT after deployment. This ensures the necessary post-deployment manual step isn’t missed.
• Develop Against a Representative Environment: If possible, configure your development environment’s DQS connection manager to point to a DQS instance that closely mimics your target environment early in the development cycle, or at least before final packaging for deployment. This can help catch environment-specific issues sooner.

By acknowledging that the DQS Cleansing component couples tightly to an environment-specific Knowledge Base ID and planning for the required configuration refresh during deployment, you can significantly reduce or eliminate these specific cleansing failures. Automating this refresh step, though complex, is the most robust solution for frequent, automated deployments. For less frequent deployments, the manual workaround described above is effective and necessary.

Implementing these strategies requires coordination between SSIS developers, DQS administrators, and deployment teams to ensure that Data Quality Knowledge Bases are managed consistently across environments and that SSIS deployment processes account for the environment-specific nature of the DQS Cleansing component’s configuration.

Further Considerations¶

While the Knowledge Base ID issue is a primary cause of DQS Cleansing failures upon deployment, other factors can also lead to problems:

• DQS Server Connectivity: Ensure the SSIS server can connect to the DQS server instance via TCP/IP and that necessary firewall rules are in place. The DQS Connection Manager should be tested.
• User Permissions: The account running the SSIS package needs sufficient permissions on the target SQL Server instance to connect to the DQS_MAIN database and execute the necessary DQS procedures.
• DQS Installation and Configuration: Verify that Data Quality Services is correctly installed and configured on the target SQL Server instance. This includes installing the DQS server components and completing the DQS database schema creation using DQSInstaller.exe.
• Knowledge Base Availability: Confirm that the required Knowledge Base exists on the target DQS server, even if its ID is different. Use the Data Quality Client tool to connect to the target DQS server and verify the KB is listed and published.
• Data Type and Mapping Mismatches: Ensure the data types and column mappings in the SSIS data flow match the domain types and mappings configured in the DQS Cleansing component and the Knowledge Base. Errors here might manifest differently but are worth checking during troubleshooting.

Addressing the KB ID issue is fundamental when deploying SSIS packages with DQS Cleansing across environments. Once this is resolved, investigating other potential causes like connectivity, permissions, or configuration errors becomes more straightforward.

Effectively managing SSIS packages that integrate with external services like DQS across different environments requires careful planning and attention to configuration details. The dependency on the internal DQS Knowledge Base ID is a specific example of how environment-specific settings can break package functionality if not handled correctly during deployment.

Have you encountered this issue when deploying your SSIS packages with DQS Cleansing? How did you address it in your organization? Share your experiences or alternative solutions in the comments below.