locked
Moving an SSIS DQS transformation between environments RRS feed

  • Question

  • 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?  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.

    Tuesday, March 29, 2016 7:51 PM

Answers

  • Someone else has felt your pain and written about it https://intelligentsql.wordpress.com/2014/01/14/ssis-dqs-the-knowledgebase-does-not-exist/ . I think that this is a bug in SSIS (thus I agree that the discussion belongs here).  The Data Quality transformation should use the KB name not an internal id.  It is the equivalent of a data source storing the object id for the destination in an ole db destination.  How stupid would that be?  Also they need to provide a means to set the kb name in the expressions for the data flow.

    I would submit a Connect case, but I'm still not convinced that Microsoft actually reads them before closing them.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, March 30, 2016 11:45 PM
  • https://connect.microsoft.com/intro/

    Arthur

    MyBlog


    Twitter

    Thursday, March 31, 2016 2:14 PM

All replies

  • Hi dgr4277,

    Looks like this question is aging without replies.

    Based on what I know, the recommendation you gotten is the proffered way, somebody outside the development then needs to take care of, so it appears to be your organizational challenge rather.

    If you do not want to close it just yet, I offer it moved to the dedicated DQS section of the forum.


    Arthur

    MyBlog


    Twitter

    Wednesday, March 30, 2016 7:05 PM
  • Hmm, its more of a SSIS transformation question than a DQS question. the problem is that the SSIS transformation hard codes the ID of the KB instead of the name of the KB
    • Edited by dgr4277 Wednesday, March 30, 2016 7:13 PM
    Wednesday, March 30, 2016 7:10 PM
  • Someone else has felt your pain and written about it https://intelligentsql.wordpress.com/2014/01/14/ssis-dqs-the-knowledgebase-does-not-exist/ . I think that this is a bug in SSIS (thus I agree that the discussion belongs here).  The Data Quality transformation should use the KB name not an internal id.  It is the equivalent of a data source storing the object id for the destination in an ole db destination.  How stupid would that be?  Also they need to provide a means to set the kb name in the expressions for the data flow.

    I would submit a Connect case, but I'm still not convinced that Microsoft actually reads them before closing them.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, March 30, 2016 11:45 PM
  • Thanks for the link.  How do I open a Connect case?
    Thursday, March 31, 2016 2:04 PM
  • https://connect.microsoft.com/intro/

    Arthur

    MyBlog


    Twitter

    Thursday, March 31, 2016 2:14 PM
  • Thanks Arthur and Russell! I really appreciate the help
    Thursday, March 31, 2016 2:16 PM