SSIS Best Practices-Naming conventions

· 3 min read

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.

TaskPrefix
For Loop ContainerFLC
Foreach Loop ContainerFELC
Sequence ContainerSEQC
ActiveX ScriptAXS
Analysis Services Execute DDLASE
Analysis Services ProcessingASP
Bulk InsertBLK
Data FlowDFT
Data Mining QueryDMQ
Execute DTS 2000 PackageEDPT
Execute PackageEPT
Execute ProcessEPR
Execute SQLSQL
File SystemFSYS
FTPFTP
Message QueueMSMQ
ScriptSCR
Send MailSMT
Transfer DatabaseTDB
Transfer Error MessagesTEM
Transfer JobsTJT
Transfer LoginsTLT
Transfer Master Stored ProceduresTSP
Transfer SQL Server ObjectsTSO
Web ServiceWST
WMI Data ReaderWMID
WMI Event WatcherWMIE
XMLXML
ComponentPrefix
DataReader SourceDR_SRC
Excel SourceEX_SRC
Flat File SourceFF_SRC
OLE DB SourceOLE_SRC
Raw File SourceRF_SRC
XML SourceXML_SRC
AggregateAGG
AuditAUD
Character MapCHM
Conditional SplitCSPL
Copy ColumnCPYC
Data ConversionDCNV
Data Mining QueryDMQ
Derived ColumnDER
Export ColumnEXPC
Fuzzy GroupingFZG
Fuzzy LookupFZL
Import ColumnIMPC
LookupLKP
MergeMRG
Merge JoinMRGJ
MulticastMLT
OLE DB CommandCMD
Percentage SamplingPSMP
PivotPVT
Row CountCNT
Row SamplingRSMP
Script ComponentSCR
Slowly Changing DimensionSCD
SortSRT
Term ExtractionTEX
Term LookupTEL
Union AllALL
UnpivotUPVT
Data Mining Model TrainingDMMT_DST
DataReader DestinationDR_DST
Dimension ProcessingDP_DST
Excel DestinationEX_DST
Flat File DestinationFF_DST
OLE DB DestinationOLE_DST
Partition ProcessingPP_DST
Raw File DestinationRF_DST
Recordset DestinationRS_DST
SQL Server DestinationSS_DST
SQL Server Mobile DestinationSSM_DST

I will be covering more on SSIS best practices in future posts. Questions, comments and feedback welcome.

Share this post