In a nutshell, the ETL process we're using right now runs an SSIS package that reads a day's worth of data from our source database, puts it into a staging database, runs a stored procedure that contains a number of hard coded business rules, then another stored procedure sends the data to the data warehouse (applying more business rules).
What I'm thinking of doing is creating a table to store our business rules, along with a description, effective date range, etc. The rules would be stored as either a T-SQL string or in a stored procedure which would be executed dynamically against the data in our staging database.
Does this process seem to make sense? Anything I should keep an eye out for? Ideas to improve the process? Other approaches I can consider to apply business rules to the data? I'm just looking for any thoughts or feedback that people might have.