I'm using SQL Server 2012 and we have created an SSIS package that uses the Data Quality Services (DQS) Cleansing transformation. Everything works great, except when we try to move the SSIS package from the DEV environment to QA or PROD.
The SSIS DQS transformation hard codes the ID of the knowledge base, and not the name of the KB, in the XML. The problem is that when you install/import a DQS KB it creates a different ID for the KB. So the ID of the KB in one environment will be different than the ID of the same KB in another environment, e.g. in the DEV environment the ID is 100005 and in the PROD environment the ID for the same KB is 100007.
Because of this when the transformation is executed in the PROD environment the task throws an error because the transformation is looking for the KB with the ID of 100005 and in PROD the ID is 100007 (the error thrown is: The Knowledge Base does not exist).
The only workarounds Microsoft suggests to only have one KB and have all environments point to it (the Network team won't allow this) or to not use the catalog/deploy tool and move the SSIS project to the other environment and then open the package in SSIS and re-select the KB in the SSIS DQS transformation, but that's not an acceptable work around because the development team doesn't have access to the server in PROD.
Has anyone figured out a different work around? BTW, this is what Microsoft says to do: https://support.microsoft.com/en-us/kb/2882914 (basically what I wrote above). I have also tried to changed the hard coded ID in the XML but for some reason that didn't work.
If there isn't work around then this task is useless, which is unfortunate because it works well.
