I hope everyone had a wonderful July 4th. l want to end this week with one nice piece of information on the naming conventions used in SSIS. There are different conventions used by different organizations but the ones below seems to be standard across lot of organizations. Using a standard naming convention in your work shows level of professionalism and it makes it easy for others to understand your ETL work. Use these standards when naming any object or if you find an older object that doesn’t follow these standards. This approach does not help a great deal at design-time where the tasks and components are easily identifiable but can help a lot at debug-time and run-time.
Here is some general rule to naming package.
- The package name gives a clear indication of what the package does.
- The casing adheres to common development practices
- It helps to organise your packages at design-time into projects that serve a similar purpose.
- It gives a visual clue as to which packages do similar works instead of what a package does in isolation.
- It provides some context in which the package operates.
- It ensures that no two packages have the same name. This can be important when you deploy the packages to your test/QA/live environments – particularly if they are deployed to the same place.
Below is the list of the objects and their standard names.
| 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 |
| 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 |
I will be covering more on SSIS best practices in future posts. Questions, comments and feedback welcome.
