sradez_DBA (8/13/2012)
Thank You All for sharing your thoughts.
As i said the project is interesting.What makes it interesting is if you have 9 different companies who want to share there products and they have there own FileSystem and DB but share a common UI.
A dynamic definition could be provided to this by either an XML file or a database table. If, for example, you store the query for the data as a text blob in a table (or XML), and store the output directory in there, then those could be called dynamically by the CLR or SSIS. Like a configuration file.
If the data will be in a single table/view, then just the object name (and maybe the schema name) will be enough to build a dynamic query. Otherwise, store the query as text and run it dynamically. The CLR or script object can dynamically determine what to write based on the dataset it gets back, if you make it smart enough to do so. Not that tricky to code.
That way, you only have to write the whole thing once, and then configure the query and directory path once per customer, instead of having to write code for each customer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon