Wednesday, August 22, 2012

My Favorite SSIS Tips and Tricks–Tip #3

This next tip is related to package properties and package GUID’s.  Whenever I create a new package (or whenever I create a package template), I always set two package properties right out of the gate:

  • I set the ProtectionLevel package property to DontSaveSensitive.  This property determines how sensitive information (passwords for connection strings, for example) are stored.  The default for this property is EncryptSensitiveWithUserKey.  This default causes sensitive information to be encrypted with the developer’s AD Windows account information.  Other developers who open the package will run into problems as any encrypted information can then not be unencrypted.  With the property set to DontSaveSensitive, sensitive information is not saved with the package.  Thus, if passwords are needed for connection strings (for example), that information must be set in the package via another mechanism such as package configurations, variables, property expressions, etc.  This is typically a better design, however, than letting sensitive information be encrypted and stored with the package.
  • I set the SuppressConfigurationWarnings package property to True.   With this property set, if the package refers to a package configuration (XML or SQL Server table) that contains settings that are not relevant for the package (such as variables that the package doesn’t contain), then warnings related to the missing settings will be suppressed.  The default for this property is False, which results in package configuration warnings that can typically be safely ignored.

As mentioned above, I set these properties in new packages and in package templates.  In most cases, my package development either starts with a package template or starts from an existing package (by copying and pasting an existing package back into the project).  As such, containers, tasks, and components end up having the same GUID values in the new package as they had in the old package (and if you are using SSIS 2005 and BIDS 2005, the package itself has the same GUID as the old package).  In some cases, this may not cause problems.  However, as several SSIS developers have discovered in the last 5-7 years, there are cases when this can cause big problems.  If two packages having components with the same GUIDs run at the same time, all types of problems can occur as the SSIS engine tracks packages and components by GUID.  And several BIDS designer issues can occur as well.  Thus, as soon as I create a new package based on a package template or prior package, I immediately reset all package GUIDs.  The easiest way to accomplish this is to use the Reset GUIDs feature of BIDS Helper (a fabulous tool that every SSIS developer in the world should be using daily).

Hope these small tips help budding SSIS developers (or even experienced developers who may not have come across these suggestions before)!

Wednesday, August 15, 2012

SQL Saturday # 154 in St. Louis–Here I come!

I’m honored to have been selected to speak at SQL Saturday # 154 coming up in St. Louis on Sep 15th!  My presentation, titled “Get Ready for SSIS in SQL Server 2012!” will be at 10:00am in Room 022 (part of Track 2).  So if you are attending and have an interest in what’s new with SSIS in SQL Server 2012, come on by!  Looking forward to a great session and a great weekend!!

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!!