This is a beta release. More documentation and a utility for building business rules will follow.

The current version of the Business Rules component supports the following rules:
- Null or Empty
- Values in a range (min/max)
- A delimited list of acceptable values
- A delimited list of excluded values
- A regular expression pattern

Design Notes:
Business rules are grouped by GroupName and are mapped to a target ColumnName.
Rules can be composed into hierarchies by mapping the BusinessRuleID with the ChildRuleID and using the MinimumScore and RuleWeight to determine whether the rule succeeds or fails.
Rules can be enabled/disabled by changing the state of the IsDisabled flag
Custom Error Messages can be define at each rule level.

Rule Example:
Company XYZ requires that all Account records have either a First and Last Name or a Company Name.

- Define a parent rule that checks ‘CompanyName’ is null. The MinimumScore for this rule would be 2 and the RuleWeight would be 2, implying that if ‘CompanyName’ is not null the rule score would be 2 and thus the rule would pass.
- Define a child rule that checks ‘FirstName’ is null. The minimum score is ignored for this rule since it is a child and the RuleWeight would be set to 1. A second child rule is created for ‘LastName’ with the same parameters at the ‘FirstName’ rule. Since both child rules are worth 1 point, both the FirstName and LastName rule must pass if CompanyName is null or the business rule would fail.

Using the Component:

Configure the component by selecting an connection to the database that either has or will have your BusinessRules table. Next you can either select the BusinessRules table or create a new table using the connection manager. You then select the RuleGroups that you want to apply to the data flow. The component two outputs Valid and Invalid. The invalid output has an additional column which contains any error messages from the business rules validation process.

Business Rule Table:

CREATE TABLE dbo.BusinessRules(
BusinessRuleID int IDENTITY(1,1) NOT NULL,
TargetColumnName nvarchar(50) NOT NULL,
AllowNull bit NOT NULL,
MinimumValue int NULL,
MaximumValue int NULL,
AcceptableValues nvarchar(max) NULL,
ExcludedValues nvarchar(max) NULL,
ValidationExpression nvarchar(max) NULL,
ErrorMessage nvarchar(255) NULL,
MinimumScore decimal(3, 2) NOT NULL,
RuleWeight decimal(3, 2) NOT NULL,
IsParent bit NOT NULL,
ChildRuleID int NULL,
RuleGroupName nvarchar(50) NOT NULL,
IsDisabled bit NOT NULL,
IsPrequisite bit NOT NULL,
BusinessRuleID ASC

Last edited Mar 2, 2013 at 2:49 PM by cprice79, version 4