SSIS - Why Naming Convention Is Important In SSIS Package


Our team has deployed different SSIS Packages those are running in Production. The team had made sure that the logging and error notification is implemented. Once in a while when our SSIS Package/s fail in Production, We get an email with error e.g. Execute SQL Task 1 failed with bla bla error and this error is also logged in text file. By looking at the email or log file, we can't really tell what exactly Execute SQL Task 1 was doing and what was the position of this Task in the SSIS Package, even it is every first or the last task in the SSIS Package.


SSIS log the information by using the Name of Task, Transformation, Container, Variable and Connection Managers. If you will have proper name of your Task, Transformation etc. then in case your SSIS Package fail, the Package will use the name of Task to log information in log file, SQL Server Agent Job or even if you have used TaskName variable in Event Handler to send an email. 

So what is the best practice to rename SSIS Tasks, Transformation, Containers, Connection Managers? Is there any document available by Microsoft that I can use? 

I did not see any specific document from Microsoft that tells you what name is the best for your Transformation or Task etc. 

Every company has it own standards. If your company do not have any standard document for naming SSIS Package items, brain storm with other developers and team members and created one so team will be using one document to name the objects.

When I name the Items in SSIS , I divide that into two parts.
  1.  The Actual name provided by SSIS such as Execute SQL Task, Data Flow Task, OLE DB Connection Manager, For Each Loop Container etc.
  2. The Purpose for what I need to use this Item.
If you and your team decided to have the original name as it is and add the purpose after adding the underscore ( _ ) that is fine too. 
Fig 1: Naming Convention in SSIS 

If your team feels that the original name is too long and it can be short or abbreviated that is still fine as long as your team follow the same style. In my case I always abbreviate the original name and write more about purpose what this task is doing. By doing that I can track the error quickly upon error notification.

Fig 2: How to use Naming Convention in SSIS 

I have shortcut the orginal name of task the way I wanted but still I can make sense from the name what this task is about. There is no right or wrong when you create short names. You might like Execute SQL Task as ESQL and I used ExecSQL. Rename the way you want but share with team so everybody use them and write good description (purpose)  so it will save some time for debugging.

No comments:

Post a Comment