Sunday, August 5, 2012

My Favorite SSIS Tips and Tricks–Tip #2

To continue on with the series of SSIS Tips and Tricks, my next tip is to decide on and use naming conventions.  This comes in two forms.  The first is for your SSIS projects and packages while the second is for your package tasks and data flow components.

For SSIS projects and packages, I’ve been using a dotted naming convention for several years now.  My project names tend to have two components – typically an overall database name, functional name, etc. followed by a categorization of what the packages in the project will do.  So, for example, if I’m working on a project to load a data mart that has been dubbed “FinMart”, then I might have one project named “FinMart.Dim” and another named “FinMart.Fact”  The first project would then (obviously) contain packages for loading the dimension tables in the data mart while the second would contain packages for loading the fact tables.  Both of these projects might live within their own separate Visual Studio solutions (in which case the solution names would follow the project names) or they might be combined into a single solution (which would then be named based on the first part of the project names – so in this example, the solution would be named “FinMart”).  Packages within each project would then be based on the project names.  I typically use a four-part dotted name for my packages in order to add a “subject” and “verb” for each package name.  Thus, continuing the example above, if I had a package for loading a dimension table based on an OLTP system named “DailyOps”, then the package might be named something like “FinMart.Dim.Vendor.LoadFromDailyOps.dtsx”.  A package for loading a fact table might be named “FinMart.Fact.PurchaseOrder.LoadFromDailyOps.dtsx”.  So, to put the examples together, I would end up with the following:

Component Name
Visual Studio Solution FinMart
Visual Studio Project FinMart.Dim
SSIS Package FinMart.Dim.Vendor.LoadFromDailyOps.dtsx
Visual Studio Project FinMart.Fact
SSIS Package FinMart.Fact.PurchaseOrder.LoadFromDailyOps.dtsx

With this naming convention, it is extremely easy to determine what solution and project a package came from and what it does.  If you have dozens of different solutions and projects with hundreds of packages, having a naming convention that you use consistently is a must.  One quick thing to mention about this naming convention, however – the @[System::PackageName] variable does not like periods in a package name, so it replaces any it finds with spaces.  Thus, for the dimension loading package listed above, the variable will contain the value “FinMart Dim Vendor LoadFromDailyOps”.  Keep that in mind when you are using package logging or using the @[System::PackageName] variable to roll your own auditing and logging structures and reports.

The second part of your naming conventions concern package containers, tasks, and data flow components.  Jamie Thomson (SSIS expert and blogger extraordinaire) first suggested using prefixes when naming package components and I wholeheartedly agree.  Jamie even provided a good list of potential prefixes to use.  I suggest you adopt Jamie’s list (perhaps customized a bit if you don’t like one or more of his suggested prefixes) and use it religiously.  I personally like to include a “dash” between the prefix and name of the component just for readability.  Thus, if I have a Sequence Container that holds tasks for loading a vendor table, the Sequence Container might be called “SEQ – Load Vendor Table”.  Likewise, a Data Flow Task within that Sequence Container might be named “DFT – Load Vendor” and an Execute SQL Task for running an auditing stored procedure might be named “SQL – Audit Vendor Load”.  Using this type of naming convention helps quickly identify components (not everyone has the icons associated with every container, task, and data flow component memorized) and helps identify what type of component executed if you use package logging (since events logged will include a component name, but not a component type).

So, to sum up – define naming conventions for your packages (and VS solutions/projects) and for the components within your packages.  And then stick to them!!

No comments: