Table-driven dynamic SQL

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

  • This was removed by the editor as SPAM

  • Thank you for the feedback. I hadn't thought about the possibility of conflicting rules. Perhaps I'll add something to make sure that an order is followed so that there's a way to resolve a conflict if needed. As for having someone who understands both the rules and SQL... as soon as I get a good understanding of them I imagine I'd be the only one for now. But the plan is to have a solid description in each rule so other Systems people can do maintenance as required. We're a small department so there'd only be 3 or 4 of us who would need access to the table.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply