0

Has anyone worked on a Business Rule Validator engine which validates business rules for an SSIS package, such that business rules are maintained outside the package?

At the moment we create a Derived Column task and evaluate all business rules in there, one derived column per business rule. These derived column values are then checked later to decide if rows are dropped (and logged) or passed on for further processing.

The concern is that any change in Business Rules makes us change the package. We'd want to make the business rules sit outside the package, such that they are configurable and can be evaluated dynamically.

Has anyone worked on such a Business Rule Editor for SSIS packages (The challenge would be how to validate it - whether the syntax we allow is T-SQL or .net)? And then there would be a custom component within the SSIS package which will interpret these rules and validate rows dynamically.

ajeesh k
  • 93
  • 8
  • I put all my business rules in stored procedures. You need to store and change them somewhere. I don't know of any tool that converts 'business rules' to a technical language such as SSIS expressions or T-SQL. Is the concern that you don't want business users editing SSIS packages? The problem is, business rules are complex and they need to be defined in something equally as complex. – Nick.Mc Jun 19 '17 at 05:10
  • We're working on a product and we wouldnt want someone to change the Core product business rules, instead they should just be able to turn them off, where required. However they can add new rules. PS: Thanks for your 'complex things are represented complex' comment. I've rephrased that requirement of mine in the question! – ajeesh k Jun 19 '17 at 05:54
  • Business Rules is a very broad statement. If you want to turn them on or off you could get your package to check beforehand in a configuration database. Personally I would just move it all into stored procedures and staging tables and use a config table directly. – Nick.Mc Jun 19 '17 at 06:34
  • DQS is another option but hasn't been shown the love by MS. – Rich Jun 19 '17 at 08:04
  • 1
    Sounds like a simple matter of programming to me, and like something that's more likely to be proprietary and not shareable. – Tab Alleman Jun 19 '17 at 13:29
  • You're right Tab, its going to be some programming. Even if the code cannot be shared, ideas are welcome too! – ajeesh k Jun 20 '17 at 08:53

0 Answers0