31 Days of SSIS
We’re at the thirty-first, and last, post for the 31 Days of SSIS. The series started with an introductory post. This series has been a collection of the things that I’ve picked up over the years in my work with SSIS. I figured I’d end this series with a best practice that I’ve not often been a fan of and am now fully committed to.
The best practice I am talking about is the use of naming conventions for tasks and transformation in SSIS packages. Through this practice, the names of tasks and transformations will always start with an acronym or abbreviation followed by a meaningful name for the item. In the place of spaces, underscores are used in the names.
For a list of the naming conventions that I typically use, I will point you to Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. About half way down the post, are the naming conventions. The rest of the post is worth reading as well, if you haven’t.
Practice Impact
As I’ve worked with SSIS packages, I’ve found a few areas where this provides benefits. Whether developing new packages or maintaining and troubleshooting existing SSIS package, you will find an appreciation for this best practice.
- Development Impact: From a development perspective there is marginal immediate benefit for using this best practice. The only impact is the naming of the task or transformation to specific convention. If you don’t typically name the objects in SSIS packages, start naming them and make it meaningful.
- Maintenance Impact: When maintaining an SSIS package, the use of a naming convention will help two-fold. First, the prefix will provide an instant indication of the context of the type of work being done. Second, the meaningful name portion of the name provides context for the work itself. When adding in new functionality, this recognition will help reduce the time it takes to understand yours or someone else’s code that may have been written weeks or months ago.
- Troubleshooting Impact: As I mentioned in the last bullet the naming convention provides an immediate level of understanding of the type of task and the context. When troubleshooting, not having to crack open code and investigate configuration settings for every object is critical to solving issues efficiently. True, you may still end up examining everything. With the use of a naming convention, when you are looking at Billy’s or Sally’s SSIS packages, you can get a leg up on troubleshooting by having context when they are not around.
Convincing Me, Convincing You
After reading through the impacts, are you convinced? Maybe not, and until a few months ago neither was I. To conclude this best practice, I want to go through a couple of my experiences with naming conventions. This should help convince you that this best practice is worth the effort, as these experiences did for me as well.
Looking At My Package
I wrote an SSIS package a while back. This was a fairly simple package. I had a Data Flow task that was named similar to “Insert Rows to SalesOrder”. A OLE DB destination within the data flow was named similar to “Insert Rows”.
In the context of the developing the SSIS package, these names seemed perfectly reasonable. The package was tested, signed off on, and deployed. Of course, these actions don’t always guarantee success when it hits production.
After a few weeks the package did what packages do when you forget about them. The package failed and I had to take a look at the log files. Opening up and digging into the SSIS package revealed that the error occurred on “Insert Rows to SalesOrder Insert Rows”.
Since I was no longer working on this package, I had to think some and hunt some to figure out where the error occurred. Was the error in the task “Insert Rows” or “Insert Rows to SalesOrder”? Was the transformation “SalesOrder Insert Rows” or “Insert Rows”? These names may seem simple to figure out when developing the package, but after some time you may forget how you designed the package.
The names of objects in your SSIS packages should help you to immediately identify where to look and start with your troubleshooting. Would you be able to better remember what you were doing if the data flow task was named “DFT_InsertRowsToSalesOrder” and the OLE DB destination was named “OLE_DST_MSS_InsertRows? Then the error message would say the error occurred at DFT_InsertRowsToSalesOrder OLE_DST_MSS_InsertRows.
Looking At Your Package
You probably won’t always be working with your own SSIS packages. Sometimes you will have to troubleshoot or maintain a package that someone else developed. In these cases, using the naming convention best practice will be worth it’s weight in gold. Probably more since SSIS package aren’t that heavy.
A while back I was monitoring some SSIS packages that had been deployed to production. Most of these packages had been written by others. Since I was on the administration side of the world, it was under my duties to troubleshoot packages when they failed.
One day, a package did fail. Actually, a lot of packages failed. Some schema changes had occurred and they were causing havoc in the SSIS packages. Due to these failures, I needed to investigate the packages and determine what needed to be done to resolve the issues.
Opening one of the first log files revealed that there was an error at “Data Flow Task 1 1 Derived Column 1 2”. Where, oh where did this error go wrong? Of course, the log file does provide the reason for the error. But it doesn’t do much to tell me the context for the error. In fact, based on these names, I already expect a mess inside the package. The names provided weren’t meaningful, and the purpose could only be discerned by reverse engineering what the component does.
Had the error occurred at “DFT_ProcessHumans DC_AddIsZombieFlag” then I would have been better equipped to troubleshoot the package. I also, probably wouldn’t be calling you at 2 AM in the morning so you can tell me what this package is supposed to be doing.
Naming Convention Wrap-Up
Hopefully, you’ve taken some time to consider this best practice. I’m a convert for exactly the reasons I’ve stated above. You should buy into this as well. It’s not just about having meaningful names. It is also about leaving behind SSIS packages that others can troubleshoot and maintain.
Related posts:
