January 26, 2012 at 12:06 am
Comments posted to this topic are about the item Process Support Database Framework
January 26, 2012 at 5:33 am
I might be way off base here -- and I don't wish to downplay any of the work you have presented -- but how is this significantly different from the SQL Agent running some SSIS packages?
The SQL Agent already takes care of scheduling, history, logging and notification (and can even manage basic stepped, batch-style tasks), with SSIS providing the flow control and reusable blocks that would probably comprise your diagrams.
I can see how your structure and procedures might be of some use in some other RDBMS but, in SQL Server much of this is already taken care of.
What notable benefits does your solution provide over the SQL Agent?
January 26, 2012 at 5:51 am
valuable discussion when viewed more from the conceptual rather than strictly implementation perspective. That aside, how reusable are components developed at the SSIS and SqlAgent level alone?
January 26, 2012 at 6:14 am
organic coder (1/26/2012)
valuable discussion when viewed more from the conceptual rather than strictly implementation perspective. That aside, how reusable are components developed at the SSIS and SqlAgent level alone?
For SSIS, extremely re-usable if you know what you're doing. Not so much if you are new to SSIS.
We have an ssis framework in place at work that allows us to quickly ramp up new clients with HRIS integration, usually limiting our development time down to about 20 hours per client (for custom business logic, and custom validation).
I think the question that was posed is a valid question...it seems like an awful lot of work to put all of this together and basically rely heavily on stored procedures, and re-utilizing template stored procedures, when something like this can just as easily be handled with SSIS, SQL Agent, and checkpoints.
January 26, 2012 at 6:45 am
It is interesting to see such an article published on a home grown process framework.
What is interesting to me is how close this solution is to the one I've developed independently, improved and been using for the last decade.
When I have presented this in the past to SQL user groups the SSIS question has often come up. My answer is that it can be used both independently and in conjunction with SSIS.
In general SSIS is processed RBAR, Row by Agonizing Row. There are times this is necessary and possibly beneficial. However, a process framework makes it very easy to process sets of data using stored procedures.
In my experience the performance and flexibility of a process framework far outperforms SSIS. It is easier to maintain, easier to have reusable components, easier to make dynamic and flexible processing rules. Please do not confuse this with me saying that these things can't be done in SSIS.
My advice to ETL developers is to look at this article and if appropriate try using a process framework. It is another tool in the tool box.
January 26, 2012 at 7:57 am
Well done! I had created a process framework at my last company that was similar in it's approach but more generic in nature. I used a queueing mechanism and it would support SSIS and stored procedures. I really enjoyed the thought that went into this article.
My process was driven by the need to have specific processes occur on a regular basis and all the third-party software was VERY EXPEN$IVE!. Their software offered more features and configuration options, but mine was cheaper (cost of my labor) and worked quite well.
Five stars!
--Paul Hunter
January 26, 2012 at 8:11 am
I ended up developing a framework for the SQL Server system that I develop and manage. I just happened to observe patterns in the processes I was developing and built the framework as the means to performing them. I didn't set out to do this at first; the main impetus was that I don't like repeating code. (Repeated code is a big problem in a system that I inherited when I took my job.) My solution works basically on a workflow motive. At this point it is implemented entirely in T-SQL; I'm looking into SMO and CLR code for future development. I would consider SSIS, but I develop and do source-control in Visual Studio 2010, and SSIS isn't integrated into Visual Studio 2010.
Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv
January 26, 2012 at 8:44 am
jimbobmcgee (1/26/2012)
What notable benefits does your solution provide over the SQL Agent?
The benefit is it provides a solution when you don't have SSIS 🙂
I agree with what you say, I would rather leverage SSIS, although in my current situation I have a SQL database (provided by a university) but I can't use SSIS. So for me this framework is quite useful.
Tony
January 26, 2012 at 8:02 pm
PocketJoshua (1/26/2012)
It is interesting to see such an article published on a home grown process framework.What is interesting to me is how close this solution is to the one I've developed independently, improved and been using for the last decade.
When I have presented this in the past to SQL user groups the SSIS question has often come up. My answer is that it can be used both independently and in conjunction with SSIS.
In general SSIS is processed RBAR, Row by Agonizing Row. There are times this is necessary and possibly beneficial. However, a process framework makes it very easy to process sets of data using stored procedures.
In my experience the performance and flexibility of a process framework far outperforms SSIS. It is easier to maintain, easier to have reusable components, easier to make dynamic and flexible processing rules. Please do not confuse this with me saying that these things can't be done in SSIS.
My advice to ETL developers is to look at this article and if appropriate try using a process framework. It is another tool in the tool box.
First, if you've had something that long that works so well, why aren't you trying to monetize it and make yourself some money? Myself, and I'm sure others, would be more than willing to check it out and if it works that well, pay accordingly.
I'd really be curious to see some benchmarking done with your framework versus a similar implementation with SSIS. I've used SSIS for the past several years to fairly decent effect on large data sets, but I'm always open to learning new things. My viewpoint tends to be a little skewed towards using SSIS because of this. Yes, there are things that I'd much rather do using T-SQL, but in most of the environments that I've worked in, SSIS tends to be the more "acceptable" approach.
January 27, 2012 at 7:04 am
Does anyone have source code for this?
January 27, 2012 at 7:35 am
Unfortunately, the code I developed is the property of my employer, so I'm not at liberty to share it. I would be interested in helping to develop an open-source solution. My solution is currently entirely in T-SQL that is copied into each database, but I'm looking at using SQL Server Management Objects to reduce the amount of T-SQL used.
Jay Bienvenu | http://bienv.com | http://twitter.com/jbnv
January 27, 2012 at 4:39 pm
jbnv, are you serious about working on an open source version? Do you think it is worthwhile?
I've actually been trying to build a SQL/table based workflow engine with a similar design for years, and have long contemplated open sourcing what I have so far, frankly because I don't have time to finish it and turn it into a commercial product, and secondly because nobody I knew showed any interest or support.
Personally, I love this little framework I was hacking on - and I like yours too. It's a pretty generic pattern actually, but it can do so many things.
I got so far as to be able to render workflow graphs from the database, and handle branching, multiple statuses, etc. and it was modeled *very* roughly after Petri nets.
This same framework is totally reusable all over the place too, not just in ETL.
Thoughts? Anybody else interested?
January 28, 2012 at 8:53 am
I'd be interested in throwing in on an open source effort. I agree that it can do much more than only SSIS. I look forward to sharing my ideas on this project.
--Paul Hunter
January 28, 2012 at 11:47 am
I would also love to participate in such a project. I think this type of framework would establish some great control yet allowing flexiblity and encouraging TEAM development on projects in any group. The analytics group I have supported for the last 6 years has technical "savvy", in that they have incredible vision to get data-driven projects off the ground, and enough SQL server skills to get themsevles killed, but they don't have a processing framework in which they can "plug-in" to. What ends up happening instead is alot of "satellite" processes in which I end up supporting, which leads to unnecessary data redundancy and server contention due to "duct-taped" architecture and processes.
Does anyone have any source code that is similar to Leo Peysakhovich's ERD?
Any help would greatly be appreciated!
Regards,
Jordan M. Stone
Wells Fargo - Capital Markets
314-719-1325
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply