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

ETL Standards Expand / Collapse
Author
Message
Posted Thursday, March 07, 2013 3:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:09 PM
Points: 98, Visits: 1,097
Hi Guys,

I've been tasked with creating an ETL framework document that contains a set of standards that our company will have to follow. My experience in SSIS isn't huge so I wanted to get an idea of what people thought should be in that document.

Things like naming conventions are an obvious one but any other suggestions would be greatly recieved.

Thanks
Post #1428311
Posted Thursday, March 07, 2013 8:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 6:23 AM
Points: 1,135, Visits: 1,149
Just a couple of ideas:

For naming conventions I go with the suggestions in Jamie Thomson's blog: http://consultingblogs.emc.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Suggested-Best-Practices-and-naming-conventions.aspx

You might also want to have some type of standards for auditing/logging fields on your DW tables (e.g. LastUpdatedDate, CreateDate, ETLUpdateProcess, ETLCreateProcess, ...)

Take a look at some of these SSIS best practices blogs: http://bi-polar23.blogspot.com/2007/11/ssis-best-practices-part-1.html

HTH,
Rob

Edit: Sorry, I misread and thought you were trying to define a standards document rather than create a Framework. I'd recommend taking a look at Andy Leonard's blog: http://sqlblog.com/blogs/andy_leonard/default.aspx for his ETL framework example.
Post #1428349
Posted Thursday, March 07, 2013 8:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:09 PM
Points: 98, Visits: 1,097
Thanks for your sugestions Rob.

It's a mix between a framework and standards document but I'll take a look at the links you suggested and see how I go.
Post #1428354
Posted Friday, March 08, 2013 12:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:16 PM
Points: 5,986, Visits: 6,931
For SSIS, there's a few things I recommend. Most of them revolve around Configurations.

I know why standards exist and mostly that's to keep some nut with his own idea of what's best because he hates the letters x, q, and B start doing random stuff. In general I find them annoying when you start getting to that level. However, there are technique standards I personally like to see utilized.

First is configurations. Everyone needs to use the same method. If your house decides on XML docs, then everything uses XML Docs. If your house wants a SQL table, then everyone uses SQL Tables.

Next, do you force each package to have a unique configuration or can they share configurations? There's pros and cons to both.

Error controls: When used, are there particular expectations for them? Do they still need to email a team member when an error pathing is hit even though it allows a process to complete? etc.

Script work always leads to an interesting discussion, as it starts with 'do we use VB.NET or C#'? I personally can't code a lick in C# right now, I use pure VB.NET. I can read it, sure, but I'd have to get up to speed on it. From there it comes down to things like do you always use the script object for file manipulation (my preference, it's less fussy) or do you use the file system control built into SSIS?

Finally you need deployment standards. These usually include environmental system variables to switch which configuration location that a particular environment looks at. Dev points to different places than QA, for example, but you need a local static variable to inform the package where to get its configs so you can do just that. Do you create job scripts for the DBAs or do they do them for you? What are the security expectations on packages?

You can go pretty deep if you want to get into the nitty gritty, but I'd start with the high level process kind of standards first, then extrapolate more specific standards if you find that Bob over on Team Bubbles REALLY hates Bs....



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1428735
Posted Sunday, March 10, 2013 4:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:09 PM
Points: 98, Visits: 1,097
Thanks Craig that's really helpful.
Post #1429028
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse