Overview
The definition of programming guidelines and naming conventions only makes sense if the advantage outweighs the effort. One important prerequisite for this is the identification of the benefits. Readability and maintainability of code are generally in the focus of developers when talking about naming conventions. In case of SQL Server Integration Services (SSIS), however, another aspect must be considered: the logging of the execution of a package in the tab Execution Results or Progress in the development environment of Visual Studio . The execution result will be logged in a tree view. For complex packages with sub packages, For Each Loop Containers, Sequence Containers, etc., the tree structure quickly becomes long and deep. It is not possible to get an idea of the correct execution plan of tasks. And it seams to be impossible identifying quickly a task that has fired an exception. Who was not yet struggling with this supposedly simple task?!
Let’s have a look behind the scene. Visual Studio orders all tasks in the Execution Result tab by the task name and not the execution order of the tasks. The following screenshot shows the execution of a rather simple package in Visual Studio 2017:
Annotations:
- There are two Sequence Containers. The names of the containers are identical except the suffix. The container with the suffix 2 is executed prior to the container with the suffix 1.
- Both Sequence Containers contain identical Control Flow Tasks. In each container the Control Flow Task with the prefix 2 is executed prior to the Control Flow Task with the prefix 1.
- In between both Sequence Containers you find three Script Tasks. The task names are prefixed with the letters A, B and C in opposition to the execution order which is C, B and A.
Both Control Flow Tasks with type of Data Flow Task are identical. The alphabetically ordered task names do not reflect the actual execution order which is OLE-DB Source prior to Count.
Have a look at the Execution Results tab. The tasks are not ordered by the order of their execution but by their names:
Any execution result of complex transformations in SSIS must appear to be not legible. Frankly, I would like to get to know Microsoft’s thoughts on that.
The above shown screenshots are taken from Visual Studio. But is the situation different when deploying and executing an SSIS Package in SQL Servers’ Integration Services Catalogs? Reports on the package executions within the Integration Services Catalogs can be opened by
right click on project/Package > Reports| Standard Reports | All Executions
In fact, the overview site of any execution orders the log entries by the task names, too.
This leads me to the conclusion, that task names should be chosen in a way that the displayed order of executed tasks corresponds to the actual execution order of the tasks. In addition to this there are two other commonly followed naming conventions for SSIS tasks:
- Use a meaningful numbering of tasks according to their actual execution order
- Use a prefix for each task type
- Use meaningful task names
Numbering of tasks according to their execution order
When thinking of a meaningful numbering of task names we should distinguish Control Flow Tasks from Data Flow Tasks.
Control Flow Tasks
Using Numbers as a prefix ensures that tasks will be logged in the right order according to the order of their execution.
The numbering should support a four-digit range (numbers up to 9999). Smaller numbers should be entered with leading zeros. Don’t use consecutive numbers. Leaving gaps between two numbers allows for changing the execution order of tasks by minimizing the effort for the modification of task names through adjusting the numbering.
Of course, numbering tasks is time consuming, but the advantage outweighs the effort. The experiences made in multi developer projects prove to be beneficial when dealing with errors:
The following execution result shows the execution plan of the above package. All tasks are ordered according to their task name. With that the execution log appears to be much more legible than the first version in this article:
Data Flow Tasks
The execution of Data Flow Tasks is normally not logged in the execution log, but it is recommended to apply a numbering convention to Data Flow Tasks, too. In fact, the convention should be extended as stated after the next screenshot.
The first executed Control Flow Task was prefixed with the number 0110. All Data Flow tasks should be prefixed with the same number followed by a numbering for all Data Flow Tasks. This allows an unambiguous identification of any Data Flow Task in a package. When using this naming convention all Data Flow Tasks are prefixed with two numbers:
- Control Flow Nummer
- Data Flow Nummer
But in which situation proves this convention to be beneficial. In case of exceptions fired by a Data Flow Task SSIS logs the task name of the Data Flow Tasks that fired the exception. Unique names help to identify the task that has fired an exception:
Use a prefix for each task typek
Each task type has its own pictogram. However, the representation of task types is limited to the size of icons which is 16×16 pixel. Moreover, a pictogram is just a visual representation. The identification of task types in an execution log requires prefixes that indicate the type of a task.
To increase the readability, it is therefore common sense that task names should be prefixed with an abbreviation depending on the task type:
- DFT for Data Flow Task
- SCT for Script Task
- SQL for SQL Execute Task
- …
The internet provides a few lists with suggestions for such abbreviations/prefixes. The following two tables contain the abbreviations/prefixes used by me.
Prefixes for Control Flow Tasks
| Task | Prefix | Typ |
| Back Up Database Task | BACKUP | |
| CDC Control Task | CDC | |
| Check Database Integrity Task | CHECKDB | |
| Data Profiling Task | DPT | |
| Execute SQL Server Agent Job Task | AGENT | |
| Execute T-SQL Statement Task | TSQL | |
| History Cleanup Task | HISTCT | |
| Maintenance Cleanup Task | MAINCT | |
| Notify Operator Task | NOT | |
| Rebuild Index Task | REBIT | |
| Reorganize Index Task | REOIT | |
| Shrink Database Task | SHRINKDB | |
| Update Statistics Task | STAT | |
| For Loop Container | FLC | |
| Foreach Loop Container | FELC | |
| Sequence Container | SEQC | |
| ActiveX Script | AXS | |
| Analysis Services Execute DDL Task | ASE | |
| Analysis Services Processing Task | ASP | |
| Bulk Insert Task | BLK | |
| Data Flow Task | DFT | |
| Data Mining Query Task | DMQ | |
| Execute Package Task | EPT | |
| Execute Process Task | EPR | |
| Execute SQL Task | SQL | |
| Expression Task | EXPR | |
| File System Task | FSYS | |
| FTP Task | FTP | |
| Message Queue Task | MSMQ | |
| Script Task | SCR | |
| Send Mail Task | SMT | |
| Transfer Database Task | TDB | |
| Transfer Error Messages Task | TEM | |
| Transfer Jobs Task | TJT | |
| Transfer Logins Task | TLT | |
| Transfer Master Stored Procedures Task | TSP | |
| Transfer SQL Server Objects Task | TSO | |
| Web Service Task | WST | |
| WMI Data Reader Task | WMID | |
| WMI Event Watcher Task | WMIE | |
| XML Task | XML |
Prefixes for Data Flow Tasks
| Task | Prefix | Type | Supplier |
| ADO NET Source | ADO_SRC | Source | |
| Azure Blob Source | AB_SRC | Source | |
| CDC Source | CDC_SRC | Source | |
| DataReader Source | DR_SRC | Source | |
| Excel Source | EX_SRC | Source | |
| Flat File Source | FF_SRC | Source | |
| HDFS File Source | HDFS_SRC | Source | |
| OData Source | ODATA_SRC | Source | |
| ODBC Source | ODBC_SRC | Source | |
| OLE DB Source | OLE_SRC | Source | |
| Raw File Source | RF_SRC | Source | |
| SharePoint List Source | SPL_SRC | Source | |
| XML Source | XML_SRC | Source | |
| Aggregate | AGG | Transformation | |
| Audit | AUD | Transformation | |
| Balanced Data Distributor | BDD | Transformation | |
| Cache Transform | CCH | Transformation | |
| CDC Splitter | CDCS | Transformation | |
| Character Map | CHM | Transformation | |
| Conditional Split | CSPL | Transformation | |
| Copy Column | CPYC | Transformation | |
| Data Conversion | DCNV | Transformation | |
| Data Mining Query | DMQ | Transformation | |
| Derived Column | DER | Transformation | |
| DQS Cleansing | DQSC | Transformation | |
| Export Column | EXPC | Transformation | |
| Fuzzy Grouping | FZG | Transformation | |
| Fuzzy Lookup | FZL | Transformation | |
| Import Column | IMPC | Transformation | |
| Lookup | LKP | Transformation | |
| Merge | MRG | Transformation | |
| Merge Join | MRGJ | Transformation | |
| Multicast | MLT | Transformation | |
| OLE DB Command | CMD | Transformation | |
| Percentage Sampling | PSMP | Transformation | |
| Pivot | PVT | Transformation | |
| Row Count | CNT | Transformation | |
| Row Sampling | RSMP | Transformation | |
| Script Component | SCR | Transformation | |
| Slowly Changing Dimension | SCD | Transformation | |
| Sort | SRT | Transformation | |
| Term Extraction | TEX | Transformation | |
| Term Lookup | TEL | Transformation | |
| Union All | ALL | Transformation | |
| Unpivot | UPVT | Transformation | |
| ADO NET Destination | ADO_DST | Destination | |
| Azure Blob Destination | AB_DST | Destination | |
| Data Mining Model Training | DMMT_DST | Destination | |
| Data Streaming Destination | DS_DST | Destination | |
| DataReaderDest | DR_DST | Destination | |
| Dimension Processing | DP_DST | Destination | |
| Excel Destination | EX_DST | Destination | |
| Flat File Destination | FF_DST | Destination | |
| HDFS File Destination | HDFS_DST | Destination | |
| ODBC Destination | ODBC_DST | Destination | |
| OLE DB Destination | OLE_DST | Destination | |
| Partition Processing | PP_DST | Destination | |
| Raw File Destination | RF_DST | Destination | |
| Recordset Destination | RS_DST | Destination | |
| SharePoint List Destination | SPL_DST | Destination | |
| SQL Server Compact Destination | SSC_DST | Destination | |
| SQL Server Destination | SS_DST | Destination | |
| Microsoft Dynamics 365 CE/CRM Source | CRM_SRC | Source | KingswaySoft Software |
| Microsoft Dynamics 365 CE/CRM Destination | CRM_DST | Destination | KingswaySoft Software |
| Oracle Eloqua Source | ELO_SRC | Source | KingswaySoft Software |
| Oracle Eloqua Destination | ELO_DST | Destination | KingswaySoft Software |
Task names
Finally, task names should briefly describe what the task does. For example, Import Customer, Check Data Types, etc.
Naming Convention
All tasks (Control Flow Tasks and Data Flow Tasks) within a SSIS package should have a unique name. The following naming convention provides a rule for specifying a unique name:
XXXX [YYYY] ZZZ Name
with
XXXX
- Numbering of Control Flow Tasks.
- The numbering of Control Flow Tasks should support a four-digit number range (up to 9999).
- Smaller numbers should be prefixed with leading zeros.
- Don’t use consecutive numbers. Leaving gaps between two numbers allows for changing the execution order of tasks by minimizing the effort for the modification of task names through adjusting the numbering.
YYYY
- Numbering of Data Flow Tasks.
- The numbering of Data Flow Tasks contains as a prefix the number of the containing Control Flow Task with type of Data Flow (XXXX).
- The numbering of Data Flow Tasks should support a four-digit number range (up to 9999).
- Smaller numbers should be prefixed with leading zeros.
- Don’t use consecutive numbers. Leaving gaps between two numbers allows for changing the execution order of tasks by minimizing the effort for the modification of task names through adjusting the numbering.
ZZZ
- Prefix that allows identifying the type of a Control Flow or Data Flow Task.
- Refer to the above given prefixes.
Name
- Task names should briefly describe what the task does.