Share via

Troubleshoot Operational Issues in Data Quality Services (DQS)

  • Article

This article contains information about the issues that you might encounter while using Data Quality Services (DQS), and how to resolve them. Please let us know if you encounter any other issues (not documented here) while using DQS by posting it on Data Quality Services MSDN Forum.

If you want to contribute to this article, use the Edit tab at the top (require sign-in). This article will be updated as and when we come across new issues, or resolve existing issues.

While working with DQS, you might encounter any of the following issues:

Unable to connect to Data Quality Server

If you try to connect to a Data Quality Server by using Data Quality Client, you receive the following error message:

“Cannot connect to the <DataQualityServer_Name>”
Message Id: LogOnWindowFailedToConnect

This occurs if any of the following three is true:

  • You have not completed the Data Quality Server installation by running the DQSInstaller.exe after completing the SQL Server setup. The following is displayed in the error message details:

    “Cannot open database "DQS_MAIN" requested by the login. The login failed. Login failed for user.”

    To fix this issue, complete the Data Quality Server installation, and then connect to the Data Quality Server using the Data Quality Client. For more information, see Run DQSInstaller.exe to Complete Data Quality Server Installation.

  • Data Quality Server is installed in a named instance, and you have not specified the SQL Server instance name along with computer name in the Connect to Server dialog box in the Data Quality Client application, or have specified an incorrect instance name. As with database engine in SQL Server, multiple instances of Data Quality Server can be installed on a single computer, one per database engine instance. The following is displayed in the error message details:

    “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.”

    To fix this issue, specify the correct instance name along with the computer name to connect to. For example: “(LOCAL)\<Instance_Name>” if Data Quality Server is on the same computer, and “<Computer_Name>\<Instance_Name>” if Data Quality Server is on remote computer.

  • Data Quality Server is installed on remote computer, and the TCP/IP protocol is not enabled for the SQL Server instance on the remote computer where Data Quality Server is installed. The following is displayed in the error message details:

    “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.”

    To fix this issue, enable the TCP/IP protocol for the SQL Server instance on the remote computer where Data Quality Server is installed, and then connect to the server using the Data Quality Client application. For more information, see Enable TCP/IP Network Protocol for SQL Server.

Return to Top

Unable to connect to Data Quality Server after installing cumulative update or patch

If you installed a cumulative update or patch to update DQS, you might receive the following error while trying to connect to Data Quality Server using your Data Quality Client:

SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: LogOnWindowFailedToConnect
Cannot connect to (LOCAL).
--------------------------------------------------------------------------------
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65581. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.ssdqs, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

To fix this issue, follow the instructions available at [[articles:Upgrade DQS: Installing Cumulative Updates or Hotfix Patches on Data Quality Services (en-US)]].

Return to Top

Unable to connect to Data Quality Server after attaching the DQS databases

After attaching the DQS databases to the same or a different SQL Server instance containing Data Quality Server, you might receive the following error while trying to connect to Data Quality Server on the particular server using your Data Quality Client:

SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: LogOnWindowFailedToConnect
Cannot connect to (LOCAL).
--------------------------------------------------------------------------------
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65627. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.ssdqs, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

To fix this issue, you must run some Transact-SQL statements after attaching the DQS databases. To do so:

  1. Using SQL Server Management Studio, connect to the SQL Server instance where you attached the DQS databases.
  2. In Object Explorer, right-click the server, and then click New Query.
  3. In the Query Editor window, copy the following SQL statements:
    ALTER DATABASE [DQS_MAIN] SET TRUSTWORTHY ON;
    EXEC sp_configure 'clr enabled', 1;
    RECONFIGURE WITH OVERRIDE
    ALTER DATABASE [DQS_MAIN] SET ENABLE_BROKER
    ALTER AUTHORIZATION ON DATABASE::[DQS_MAIN] TO [##MS_dqs_db_owner_login##]
    ALTER AUTHORIZATION ON DATABASE::[DQS_PROJECTS] TO [##MS_dqs_db_owner_login##]
  4. Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully. You will see the following message: Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
  5. Connect to the Data Quality Server using the Data Quality Client to verify if you can connect successfully.

Return to Top

Unable to connect to Data Quality Server after installing .NET Framework Updates

After installing .NET Framework updates on your Data Quality Server computer, you might receive any of the following three errors while trying to connect to Data Quality Server using your Data Quality Client:

  • "A new version of .NET was installed on this machine. In order to continue to work with DQS please run dqsinstaller.exe –upgradedlls."
  • "A .NET Framework error occurred during execution of user-defined routine or aggregate."
    "SetDataQualitySessionPhaseTwo"
  • "Execution of the Init DQ Server stored procedure failed."

To fix this issue, you must run dqsinstaller.exe at the command prompt with the -upgradedlls command line parameter. For detailed information, see Upgrade SQLCLR Assemblies After .NET Framework Update.

Return to Top

Error: The context.DQSession argument cannot be null

When you try to connect to a Data Quality Server using the Data Quality Client application, you might receive the following error:

“The context.DQSession argument cannot be null."
Message Id: InfArgumentCannotBeNull

To fix this issue:

  1. Restart the SQL Server service for the instance where Data Quality Server is installed.
  2. Connect to the Data Quality Server using the Data Quality Client application.

Return to Top

All options are unavailable/dimmed in Data Quality Client

When you log on to the Data Quality Client application, all the options in the Data Quality Client home screen are unavailable/dimmed.

To fix this issue, ensure that the user account with which you logged on to the Data Quality Client is assigned one of the three dqs roles (dqs_administrator, dqs_kb_editor, and dqs_kb_operator) on the DQS_MAIN database. For information about doing so, see Grant DQS Roles to Users.

Return to Top

Source/destination database is not available in Data Quality Client for DQS operations

Your source/destination database will not be available in the DQS client for any of the DQS operations (knowledge discovery, cleansing, matching, and data export) if the source/destination database is not in the same SQL Server instance as the Data Quality Server.

To fix this issue, ensure that your source/destination database is in the same SQL Server instance as the Data Quality Server you are connected to.

Return to Top

Some activities are unavailable for a knowledge base

While opening a knowledge base in the Open knowledge base screen to perform some activity, you might notice that only a single activity out of the three activities (Domain Management, Knowledge Discovery, and Matching Policy) is available. This happens because the knowledge base is in the middle of an activity, and you can continue with only that activity on that knowledge base. The State field displays the granular level of the activity that is currently active for the knowledge base. Even if you unlock the knowledge base, other activities are still not available, and you have to continue with the currently active activity.

To perform any other activity on the knowledge base, you can do either of the following:

  • Open the knowledge base, complete the current activity, and then publish it.
  • Open the knowledge base, and click Cancel to end current activity. If you do this, all your work on the knowledge base pertaining to the current activity will be lost.

Return to Top

Limitation with cleansing data columns of NVARCHAR(MAX) and VARCHAR(MAX) data types in the DQS Cleansing component in Integration Services

Data columns of the NVARCHAR(MAX) and VARCHAR(MAX) data types are not supported in the DQS Cleansing component in Integration Services. As such, these data columns are unavailable for mapping in the Mapping tab of DQS Cleansing Transformation Editor, and hence cannot be cleansed.

To fix this issue, before processing these data columns using the DQS Cleansing component, you must convert them to DT_STR or DT_WSTR data type using the Data Conversion transform.

Return to Top

Error when Data Quality Client is left idle for an extended period

The following error might appear if the Data Quality Client application is left idle for an extended period of time:

SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: ApplicationUnhandledException
An unhandled exception has occurred. For more details, see the Data Quality Services client log.
--------------------------------------------------------------------------------
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at Microsoft.Ssdqs.Studio.ViewModels.Data.Common.HeartbeatDispatcher.Dispatch(Object data)
at Microsoft.Ssdqs.Studio.ViewModels.Data.Common.DispatcherBase.<>c__DisplayClass2. b__1()
at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)

This however does not lead to loss of work. Click OK in the error message to close it, and continue with your work. If Data Quality Client does not respond after closing the error message, restart your Data Quality Client application.

Return to Top

Error during mapping in Data Quality Client when source data table contains columns of certain data types

The following error might appear when you click Next in the Map stage of the knowledge discovery, data cleansing, or data matching activities:

SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: DataServiceDataSourceTableNotFound
The data source table with id '[<DATABASE_NAME>].[<SCHEMA_NAME>].[<TABLE_NAME>]' was not found
while adding to the repository. Check whether the table is accessible by DQS.
--------------------------------------------------------------------------------
Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: The data source table with id '[<DATABASE_NAME>].[<SCHEMA_NAME>].[<TABLE_NAME>]' was not found while adding to the repository. Check whether the table is accessible by DQS.
at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec()
at Microsoft.Ssdqs.Proxy.EntryPoint.MetadataManagementEntryPointClient.DataSourceMappingCreate(DataSourceMapping dataSourceMapping)
at Microsoft.Ssdqs.Studio.ViewModels.Data.DataSources.DataSourceMappingProvider.SaveDataSourceMapping(DataSourceMapping dataSourceMapping)
at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Common.DataSourceMappingsViewModel.SaveDataSourceMapping()

This error occurs because your source table contains columns of certain data types such as geography, geometry, image, and hierarchyid.

Options to fix this issue;

  1. Remove the columns of these data types from your source table, and map your source table columns again with DQS domains in the Data Quality Client application.
  2. Import the data into an Excel spreadsheet and use DQS to analyze the data from there.  Note that if you insert the data from a database source, you may find that DQS analyzes the empty rows if the complete spreadsheet has a linked datasource e.g. SQL table.  This will cause the task to take much longer than required, so save the file without the linked data by copying the data to a new file and saving.

Return to Top

Error while exporting the results during the cleansing or matching activity to an Excel sheet

The following error message might appear while exporting the cleansed data to an Excel file during the cleansing activity:

File download failed. Check that the export destination file does not already exist.

Similarly, on exporting the matching data to an Excel file during the matching activity, the following error message might appear:

SQL Server Data Quality Services
--------------------------------------------------------------------------------
Message Id: ExcelFileSheetReaderError
Failed reading data from excel sheet: 'Sheet1'
--------------------------------------------------------------------------------
Microsoft.Ssdqs.Infra.Utilities.Excel.ExcelFileException: Failed reading data from excel sheet: 'Sheet1' ---> System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.Ssdqs.Infra.Utilities.Excel.ExcelOleDBFileSheetBase..ctor(String connectionString, String sheetName, ExcelFileType fileType, Boolean columnNamesInFirstRow)
--- End of inner exception stack trace ---
at Microsoft.Ssdqs.Infra.Utilities.Excel.ExcelOleDBFileSheetBase..ctor(String connectionString, String sheetName, ExcelFileType fileType, Boolean columnNamesInFirstRow)
at Microsoft.Ssdqs.Infra.Utilities.Excel.ExcelFileSheetWriter..ctor(String connectionString, String sheetName, ExcelFileType fileType, IEnumerable`1 headerFields)
at Microsoft.Ssdqs.Infra.Utilities.Excel.ExcelFileWriter.GetSheetWriter(String sheetName, IEnumerable`1 headerFields)
at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Common.ExportExcelFileProgressDialogViewModel.ExportExcelFile(String exportProcessId, String downloadedFilePath, Int32 itemNumber, Int32 numberOfItems, BaseKnowledgebase knowledgebase)
at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Matching.MatchingFileExportProgressViewModel.DoExportWork()
at Microsoft.Ssdqs.Studio.ViewModels.ViewModels.Common.ExportExcelFileProgressDialogViewModel.ExecutionThreadMethod()

This error occurs because DQS does not allow export of the cleansing or matching results to an Excel file if you are using 64-bit version of Excel. You can export the results only to a SQL Server database or to a .csv file.

To workaround this issue, export the results to a SQL Server database or to a .csv file if you are using 64-bit version of Excel.

Return to Top

Error while running an SSIS package containing a DQS Cleansing component

One of the following two error messages might appear while running a SSIS package that contains a DQS Cleansing component:

ERROR MESSAGE 1:

Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: A fatal error occurred when trying to execute the service.
at Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec()
at Microsoft.Ssdqs.Proxy.EntryPoint.KnowledgebaseManagementEntryPointClient.KnowledgebaseStartBatchActivity(Int64 knowledgebaseId, String activityIdentifier)
at Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.PreExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

ERROR MESSAGE 2:

Microsoft.Ssdqs.Core.Context.ResponseException: A fatal error occurred when trying to execute the service. ---> System.Data.SqlClient.SqlException: String or binary data would be truncated.;
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection);
at System.Data.SqlClient.SqlDataReaderSmi.InternalRead(Boolean ignoreNonFatalErrors);
at System.Data.SqlClient.SqlDataReaderSmi.Read();
at Microsoft.Ssdqs.Infra.Database.Dao.Core.AKnowledgebaseAuditController.<>c__DisplayClass1. b__0(SqlDataReader reader);
at Microsoft.Ssdqs.Infra.Database.Dao.DaoControllerBase`5.Execute();
at Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.BaseKnowledgebaseManager.AuditKnowledgebaseActivity(IMasterContext context, BaseKnowledgebase knowledgebase, String originKnowledgebaseName, KnowledgebaseOperation activity);
at Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.BaseKnowledgebaseManager.Add(IMasterContext context, BaseKnowledgebase knowledgebase);
at Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Managers.DQProjectManager.StartBatchActivity(IMasterContext context, BaseKnowledgebase knowledgebase, String identifier);
at Microsoft.Ssdqs.Core.Service.KnowledgebaseManagement.Service.KnowledgebaseServiceConcrete.Execute(IMasterContext context, IServiceRequest request); at Microsoft.Ssdqs.Flow.Services.ExecuteServiceFlow.Process(IMasterContext context);
--- End of inner exception stack trace ---;

This error occurs because the length of the name of the data quality project created as a result of running the SSIS package is more than the maximum allowed number of characters (128) in DQS. Whenever, you run an SSIS package containing a DQS Cleansing component, a data quality project is automatically created in DQS with the following naming convention:

<SSIS Package Name>.<DQS Cleansing component name>_DateTimeStamp_{GUID}_GUID.

For example: MyPackage.DQSCleansingExample_6/9/2012 1:43:48 PM_{0E27D092-5153-4F4C-848D-7D78AC3BB672}_cf0aa4f5-3cac-431d-9a71-32e124322e3c

To resolve this issue, shorten the names of your package and the DQS Cleansing component to ensure that the length of the automatically generated data quality project name is less than 128 characters.

Return to Top

Error while running an SSIS package containing a DQS Cleansing component targeting another Data Quality Server

You might receive the following error when running an SSIS package containing DQS Cleansing component:

DFT_ProcessWorkData:Error: Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: The Knowledge Base does not exist [Id : <KNOWLEDGE_BASE_ID>]. 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)

DFT_ProcessWorkData: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)

DFT_ProcessWorkData:Error: An unexpected error occurred in the DQS infrastructure. 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)

This error occurs if the SSIS package is run against a Data Quality Server installation, which is different from the one against which the SSIS package was created. The Data Cleansing component in the SSIS package internally stores the "ID" of the knowledge base against which it is mapped instead of storing the "name" of the knowledge base. The Knowledge Base ID for the same knowledge bases is different in diffferent Data Quality Server installations. Hence, the DQS cleansing component is unable to locate the Knowledge Base ID for the same knowledge base on another Data Quality Server installation, and throws this error.

To resolve this issue, before running the SSIS package against the new Data Quality Server installation, edit the package to change the connection string to point to the new Data Quality Server. This changes the Knowledge Base ID in the package accordingly, and you will be able run the package succesfully against the new Data Quality Server installation.

Return to Top

Can't see any knowledge bases in the drop-down list of the DQS Cleansing connection manager in SSIS

While trying to set up a connection to the Data Quality Server on the local computer using the DQS Cleansing component in SSIS, you cannot see any of the knowledge bases in the drop-down list in the DQS Cleansing connection manage.

This occurs if you specified local as the server name in the connection manager. In this case, the knowledge base drop-down list will be empty:

To resolve this issue, specify the local server name as (local):

Return to Top

Error: The attempt to update or delete a DAO object of type 'AKnowledgebase' with id <ID> has failed because the object is not up to date or is being deleted from the database

You might receive this error while running a cleansing operation either from the Data Quality Client or DQS Cleansing component in SSIS.

This primarily occurs if you are running out of space on your computer, and also if you running a version of SQL Server 2012 prior to SP1. You can find detailed information about this issue here.

To resolve this issue, we recommend that you install the SQL Server 2012 SP1 release or later as space optimization for DQS was included in this update to fix the above issue. Also, consider regularly deleting the projects created while running the SSIS package containing the DQS Cleansing component that you do not require anymore to ensure you have enough space.

Return to Top

See Also