DQS Exception Issues

Recently I created several SSIS packages with DQS component to do data cleansing jobs while loading data into data warehouse.  After deploying the project to the remote server, the packages failed to execute at remote server with following exception thrown:

[DQS Cleansing [2]] Error: 
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)

After research and investigation for several days, I figured out that the SQL server version on my PC  is different from the remote sever which has SQL 2012 CU2.  I then upgraded my client machine to SQL 2012 CU2.

After that, some DQS packages will run inside the VSDT, some will still fail with the same exception as the diagram showed:

This is caused by the length of Data Quality Project Name that DQS created, which is limited to 128 characters (a combination of the package name + DQS Cleansing component name + datetime stamp + a few GUIDs).  The long project name will fail to be inserted into  [DQS_MAIN].[dbo].[A_KNOWLEDGEBASE_ACTIVITY].[KNOWLEDGEBASE_NAME].

In addition, I noticed that before CU2, the project name only append one GUID at end which allows longer SSIS package name without failing.  So, if you have experience of the DQS package failing to execute after upgrading to CU2, you may have the same problem.

Solution:

• Right click the DQS Cleansing Component to open the component property page.
  
  
• Rename the PackageName + Name of the component to be less than 30 characters.

P.S. Change the Name of the package property or SSIS package name will not change the PackageName property inside the DQS Cleansing component.

Now, the SSIS Package is able to run without exception on either client or remote server.

 

If you encountered the following error when you open the DQS client application, it is related to the server been upgraded without updating the DQS databases. You could run DQS installer with upgrade option in Command Prompt to resolve the issue.

 

DQSInstaller.exe -upgrade

Special thanks to Jason H. from Microsoft for looking into my question posted on DQS Forums.

Reference:

http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx