Is it possble to know what rule failed programatically?


  • Using C#, .net 4.5. Right now we are able to get errors from DQS and store them in separate table, but I can not figure out what rule has failed in the case of multiple rules.

    We've built a DQS wrapper and using calls like this.

    string KbIdsql = "Select Id from [dbo].[A_Knowledgebase] where name = '{0}' and type = 1";
    string DmIdsql = "Select id from [Knowledgebase{0}].[B_DATA_SERVICE_FIELD] where name = '{1}' and FUNCTIONAL_TYPE = 1";
    string ListKbSql = "select Id,Name from [dbo].[A_Knowledgebase] where type = 1 order by Name";
    string ListDmSql = "select Id,Name from [Knowledgebase{0}].[B_DATA_SERVICE_FIELD] where FUNCTIONAL_TYPE = 1 order by Name";

    Where is the data for the particular rule failing? Or it happens dynamically and data is not being store?

    I am able to store the value of the failed field but not the rule.

    thank you!


    Dienstag, 29. Oktober 2013 19:06

Alle Antworten

  • Hello,

    DQS does not support programmatic access to its operations or data; this is mentioned in the DQS FAQ here.

    (SQL Server Documentation | Twitter: @vivek_msft)

    NOTE: Please remember to appropriately vote a post as "helpful" or mark as "answer" to help the community.

    Donnerstag, 31. Oktober 2013 07:46
  • but the data is in database somewhere.

    I can extract list of rules from [DQS_MAIN].[KnowledgeBase{0}].[B_RULE]

    where {0} is an id from [DQS_MAIN].[[dbo].[A_Knowledgebase]

    and I also can easily get a DOMAIN_ID

    My task is to get a rule to the screen that failed in DQS. At this moment I can get every rule for each DOMAIN_ID and show it to user as potential list to check but not the rule that has failed.

    Our end-user is not aware of DQS running on the back.


    Donnerstag, 31. Oktober 2013 13:57
  • Had to spend some time with data today and came up with rather sloppy way of getting what I wanted. It is not overly elegant but will get the job done for now.

    declare @dot varchar(3) =''''+'.' + ''''
    declare @sql1 varchar(1000)
    declare @sql2 varchar(1000)
    declare @sql3 varchar(1000)
    declare @jobid char(10);
    declare @schemaid int;
    --get latest run project 
    select top 1 'A_KNOWLEDGEBASE',* from [DQS_MAIN].[dbo].[A_KNOWLEDGEBASE]  where name like 'gl_dqs%' order by CREATE_DATE desc
    select top 1 @jobid = ID from [DQS_MAIN].[dbo].[A_KNOWLEDGEBASE]  where name like 'gl_dqs%' order by CREATE_DATE desc
    set @sql1 ='SELECT * FROM [DQS_PROJECTS].[DQProject'+@jobid+'].[V_B_TERM]  where correction_reason_message like '''+'failed%'+''''
    select @sql1
    set @sql2 ='SELECT * FROM [DQS_PROJECTS].[DQProject'+@jobid+'].[b_rule]'
    select @sql2
    set @sql3 = 'select distinct t.c.value('+@dot +', '''+'varchar(1000)'+''') ConditionTypes, Domain_Id, correction_reason_message
    from [DQS_PROJECTS].[DQProject'+@jobid+'].[b_rule] q cross apply q.Condition.nodes('''+'//Microsoft.Ssdqs.DomainRules.Define.Condition.DBStorageCondition'+''') t(c) left join [DQS_PROJECTS].[DQProject'+@jobid+'].[V_B_TERM] proj on q.Domain_Id=proj.field_id where correction_reason_message like '''+'failed%'+''''
    select @sql3


    Freitag, 1. November 2013 13:42