During the developing of ETL for the Data Warehouse and the ETL Meta Data (EMD) framework a range of objects will need to be named and recorded in the EMD repository. To ensure that EMD objects are given meaningful and consistent names , the following set of naming conventions is provided. Below are the SSIS Naming Conventions we always use to append and prepend to the visual controls provided in BIDS. Well a truth to be said is that ETL using SSIS tends to become spaghetti or Amazonia. Worst that’s hard to debug once done deployed. Hence we used to stick to the naming conventions in all of the projects we worked on in order for us to better troubleshoot.
The acronyms below should be used at the beginning of the names of tasks to identify what type of task it is:
1. Control
|
||
Task
|
Prefix
|
|
For Loop Container
|
FRLC
|
|
Foreach Loop Container
|
FELC
|
|
Sequence Container
|
SEQC
|
|
ActiveX Script
|
AXSC
|
|
Analysis Services Execute DDL
|
ASED
|
|
Analysis Services Processing
|
ASSP
|
|
Bulk Insert
|
BLKI
|
|
Data Flow
|
DTFL
|
|
Data Mining Query
|
DMQR
|
|
Execute DTS 2000 Package
|
EDPK
|
|
Execute Package
|
EPKG
|
|
Execute Processs
|
EPRC
|
|
Execute SQL
|
ESQL
|
|
File System
|
FSYS
|
|
FTP
|
FTP
|
|
Message Queue
|
MSMQ
|
|
Script
|
SCRP
|
|
Send Mail
|
SNDM
|
|
Transfer Database
|
TRDB
|
|
Transfer Error Messages
|
TREM
|
|
Transfer Jobs
|
TRJB
|
|
Transfer Logins
|
TRLI
|
|
Transfer Master Stored Procedures
|
TMSP
|
|
Transfer SQL Server Objects
|
TRSO
|
|
Web Service
|
WSRV
|
|
WMI Data Reader
|
WMID
|
|
WMI Event Watcher
|
WMIE
|
|
XML
|
XML
|
|
2. Maintenance
|
||
Task
|
Prefix
|
|
Back Up Database Task
|
BUDB_TSK
|
|
Check Database Integrity Task
|
CDBI_TSK
|
|
Execute SQL Server Agent Job Task
|
SSAJ_TSK
|
|
Execute T-SQL Statement Task
|
TSQL_TSK
|
|
History Cleanup Task
|
HCLN_TSK
|
|
Maintenance Clean Task
|
MCLN_TSK
|
|
Notify Operator Task
|
NTFO_TSK
|
|
Rebuild Index Task
|
RBDI_TSK
|
|
Reorganize Index Task
|
RORI_TSK
|
|
Shrink Database Task
|
SHDB_TSK
|
|
Update Statistics Task
|
USTA_TSK
|
|
3. Data Flow Components - Source
|
||
Component
|
Prefix
|
|
ADO NET Source
|
ADO_SRC
|
|
Excel Source
|
EX_SRC
|
|
Flat File Source
|
FF_SRC
|
|
OLE DB Source
|
OLE_SRC
|
|
Raw File Source
|
RF_SRC
|
|
XML Source
|
XML_SRC
|
|
4. Data Flow Components - Transformation
|
||
Component
|
Prefix
|
|
Aggregate
|
AGRG
|
|
Audit
|
AUDT
|
|
Cache Transform
|
CFRM
|
|
Character Map
|
CHRM
|
|
Conditional Split
|
CSPL
|
|
Copy Column
|
CPYC
|
|
Data Conversion
|
DCNV
|
|
Data Mining Query
|
DMQR
|
|
Derived Column
|
DERC
|
|
Export Column
|
EXPC
|
|
Fuzzy Grouping
|
FZGR
|
|
Fuzzy Lookup
|
FZLK
|
|
Import Column
|
IMPC
|
|
Lookup
|
LKUP
|
|
Merge
|
MRGE
|
|
Merge Join
|
MRGJ
|
|
Multicast
|
MLTC
|
|
OLE DB Command
|
OLEC
|
|
Percentage Sampling
|
PSMP
|
|
Pivot
|
PIVT
|
|
Row Count
|
RCNT
|
|
Raw Sampling
|
RSMP
|
|
Script Component
|
SCRC
|
|
Slowly Changing Dimension
|
SLCD
|
|
Sort
|
SORT
|
|
Term Extraction
|
TEXT
|
|
Term Lookup
|
TLKP
|
|
Union All
|
UALL
|
|
Unpivot
|
UPVT
|
|
5. Data Flow Components - Destination
|
||
Component
|
Prefix
|
|
ADO Net Destination
|
ADO_DST
|
|
Data Mining Model Training
|
DMMT_DST
|
|
DataReader Destination
|
DR_DST
|
|
Dimension Processing
|
DP_DST
|
|
Excel Destination
|
EXL_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 Compact Destination
|
SC_DST
|
|
SQL Server Destination
|
SS_DST
|
|
SQL Server Mobile Destination
|
SM_DST
|
|
This is a great list! I wish I'd come across earlier :-)
ReplyDeleteI made one for myself, just in case you'd like to take a look -
http://aalamrangi.wordpress.com/2014/05/28/ssis-components-naming-conventions/