Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Process Support Database Framework


Process Support Database Framework

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 341
Comments posted to this topic are about the item Process Support Database Framework



jimbobmcgee
jimbobmcgee
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 915
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?
organic coder
organic coder
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
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?
jason.freier
jason.freier
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 168
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.
PocketJoshua
PocketJoshua
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 116
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.



SQLNightOwl
SQLNightOwl
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 503
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
jbnv
jbnv
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 513
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
nerdbiker
nerdbiker
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 121
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
jason.freier
jason.freier
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 168
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.
jstone923
jstone923
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 118
Does anyone have source code for this?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search