ETL Naming Conventions
This document guides ETL developers for the ETL Naming Conventions:
Definitions:
PascalCasing (aka Upper Camel Casing) – The first character of each word is capitalised. Example: FirstName
camelCasing – The first character of each word is capitalized except the first one Example: firstName
*UPPERCASE – All characters are uppercase. Use these identifiers only for abbreviations (eg: ADO) and for Transact SQL keywords (eg: SELECT Id FROM Table)
(reference: http://en.wikipedia.org/wiki/Naming_convention_(programming
) )
1. Package Name Convention
As the number of packages will grow during the project life, it is suggested to have naming convention for ETL package names so that the package can be manageable. The current development culture of defining namespaces for objects. Accordingly, the following package naming convention should be applied to package names:
[ProjectName].[DestinationDatabaseName].[SubjectName].[Action]_[Table Name].dtsx
OR
[ProjectName].[DestinationDatabaseName].[SubjectName].[Package_Description_Purpose].dtsx
eg.
AdventureWork.EDW.Sales.Load_Internet_Sales.dtxs
AdventureWork.EDW.Sales.Transform_Internet_Sales.dtxs
AdventureWork.EDW.Sales.Load_DimCustomer.dtxs
| Package Name Part | Case |
|---|---|
| Namespace | Pascal Case |
| Package Name | Pascal Case separated by underscores for defining packagce so they stand out in the code e.g. Load_CostData |
2. Name Convention for Tasks/Compontents
Use naming conventions for your tasks and components. I suggest using acronymns at the start of the name and there are some suggestions for these acronymns at the end of this article. This approach does not help a great deal at design-time where the tasks and components are easily identifiable but can be invaluable at debug-time and run-time. e.g. My suggested acronymn for a Data Flow Task is DFT so the name of a data flow task that populates a table called MyTable could be “DFT Load MyTable”.
The acronymns below should be used at the beginning of the names of tasks to identify what type of task it is.
| Task | Prefix |
| For Loop Container | FLC |
| Foreach Loop Container | FELC |
| Sequence Container | SEQC |
| ActiveX Script | AXS |
| Analysis Services Execute DDL | ASE |
| Analysis Services Processing | ASP |
| Bulk Insert | BLK |
| Data Flow | DFT |
| Data Mining Query | DMQ |
| Execute DTS 2000 Package | EDPT |
| Execute Package | EPT |
| Execute Process | EPR |
| Execute SQL | SQL |
| File System | FSYS |
| FTP | FTP |
| Message Queue | MSMQ |
| Script | SCR |
| Send Mail | SMT |
| Transfer Database | TDB |
| Transfer Error Messages | TEM |
| Transfer Jobs | TJT |
| Transfer Logins | TLT |
| Transfer Master Stored Procedures | TSP |
| Transfer SQL Server Objects | TSO |
| Web Service | WST |
| WMI Data Reader | WMID |
| WMI Event Watcher | WMIE |
| XML | XML |
These acronymns should be used at the beginning of the names of components to identify what type of component it is.
| Component | Prefix |
| DataReader Source | DR_SRC |
| Excel Source | EX_SRC |
| Flat File Source | FF_SRC |
| OLE DB Source | OLE_SRC |
| Raw File Source | RF_SRC |
| XML Source | XML_SRC |
| Aggregate | AGG |
| Audit | AUD |
| Character Map | CHM |
| Conditional Split | CSPL |
| Copy Column | CPYC |
| Data Conversion | DCNV |
| Data Mining Query | DMQ |
| Derived Column | DER |
| Export Column | EXPC |
| Fuzzy Grouping | FZG |
| Fuzzy Lookup | FZL |
| Import Column | IMPC |
| Lookup | LKP |
| Merge | MRG |
| Merge Join | MRGJ |
| Multicast | MLT |
| OLE DB Command | CMD |
| Percentage Sampling | PSMP |
| Pivot | PVT |
| Row Count | CNT |
| Row Sampling | RSMP |
| Script Component | SCR |
| Slowly Changing Dimension | SCD |
| Sort | SRT |
| Term Extraction | TEX |
| Term Lookup | TEL |
| Union All | ALL |
| Unpivot | UPVT |
| Data Mining Model Training | DMMT_DST |
| DataReader Destination | DR_DST |
| Dimension Processing | DP_DST |
| Excel Destination | EX_DST |
| Flat File Destination | FF_DST |
| OLE DB Destination | OLE_DST |
| Partition Processing | PP_DST |
| Raw File Destination | RF_DST |
| Recordset Destination | RS_DST |
| SQL Server Destination | SS_DST |
| SQL Server Mobile Destination | SSM_DST |
3. Variable Names
Name convention for SSIS package should be in Pascal Casing
E.g :
IsOk
IsDateEarlierThanOctober
SourceConnectionString
The following common variable names should be used across packages
SourceConnectionString
TargetConnectionString
LogConnectionString
4. Script
SSIS supports both VB.NET & C# Scripts. In HICAR project, the VB.NET Script is used across the SSIS package, particularly Script component. The following table summarizes the capitalisation rules for identifiers and provides examples for the different types of identifiers.
| Identifier | Case | Example |
|---|---|---|
| Class | Pascal | AppDomain |
| Enumeration type | Pascal | ErrorLevel |
| Enumeration values | Pascal | FatalError |
| Event | Pascal | ValueChanged |
| Exception class | Pascal | WebException |
| Read-only static field | Pascal | RedValue |
| Interface | Pascal | IDisposable |
| Method | Pascal | ToString |
| Namespace | Pascal | System.Drawing |
| Parameter | Camel | typeName |
| Property | Pascal | BackColor |
I learned a lot from this article, great help for me, thank you!
Very nice, i suggest webmaster can set up a forum, so that we can talk and communicate.
Hello, make sure you inform us when we will see a follow up.
Hi there, please inform us when we will see a follow up.
Thanks for the blog post. I thought it was interesting.
Thanks for sharing, I like this blog!
Considerably, this post is really the sweetest on this notable topic. I harmonise with your conclusions and will thirstily look forward to your incoming updates. Saying thanks will not just be sufficient, for the phenomenal clarity in your writing. I will directly grab your rss feed to stay informed of any updates. Admirable work and much success in your business dealings! Please excuse my poor English as it is not my first tongue.
I’m usually to running a blog and i really recognize your content. The article has actually peaks my interest. I’m going to bookmark your web site and hold checking for brand spanking new information.
An impressive share, I just given this onto a colleague who was doing somewhat analysis on this. And he in fact purchased me breakfast as a result of I found it for him.. smile. So let me reword that: Thnx for the deal with! But yeah Thnkx for spending the time to debate this, I really feel strongly about it and love reading more on this topic. If possible, as you grow to be experience, would you mind updating your weblog with extra details? It’s extremely useful for me. Huge thumb up for this weblog put up!
Your formidable write about, I just with all this upon any coworker who was doing a tad evaluation during this. And hubby the fact remains bought me breakfast every day as a result of I found it to get him.. teeth. Consequently ok, i’ll reword in which Thnx for your manage! Nonetheless really Thnkx with regard to shelling out enough time to go about this, I actually come to feel solidly about that as well as like researching more about the following niche. If workable, when you come to be expertise, do you mind posting your own blog page together with additional points? It’s extremely useful for my family. Big flash in place for this article!