Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Process Support Database Framework Expand / Collapse
Author
Message
Posted Thursday, January 26, 2012 12:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 138, Visits: 269
Comments posted to this topic are about the item Process Support Database Framework


Post #1241937
Posted Thursday, January 26, 2012 5:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:50 AM
Points: 62, Visits: 751
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?
Post #1242083
Posted Thursday, January 26, 2012 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:41 AM
Points: 1, 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?
Post #1242095
Posted Thursday, January 26, 2012 6:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:04 PM
Points: 15, Visits: 110
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.



Post #1242107
Posted Thursday, January 26, 2012 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:24 AM
Points: 13, Visits: 92
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.



Post #1242127
Posted Thursday, January 26, 2012 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:55 AM
Points: 201, Visits: 404
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
Post #1242193
Posted Thursday, January 26, 2012 8:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:20 PM
Points: 64, Visits: 432
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
Post #1242207
Posted Thursday, January 26, 2012 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:15 AM
Points: 20, Visits: 107
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
Post #1242256
Posted Thursday, January 26, 2012 8:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:04 PM
Points: 15, Visits: 110
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.
Post #1242565
Posted Friday, January 27, 2012 7:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:43 PM
Points: 9, Visits: 113
Does anyone have source code for this?
Post #1242821
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse