Monday, September 10, 2012

My Favorite SSIS Tips and Tricks–Tip #4

My next tip is related to a post that I created back in February 2008 (more on that in a minute).  This tip has to do with Connection Manager names.  When you create a new Connection Manager, the resulting object is typically named with two parts – the server name and the database name.  So, for example, a Connection Manager that connects to the FinMart database on a server named DevTeam01, that Connection Manager’s name will be DevTeam01.FinMart as shown below:

image

Now, this might seem fine – until you get ready to move this package to a different server for testing, QA, or even production use.  At that point, the Connection Manage name might be very misleading since it may no longer connect to the DevTeam01 server.  So, the first part of this tip is to remove the server name from your Connection Manager names…

Now, back to that February 2008 post.  That post recommended that you always create two Connection Managers for use with SQL Server databases.  The first uses an OLE DB provider (either the SQL Server Native Client or the Microsoft OLE DB Provider for SQL Server) while the second uses an ADO.Net provider (using the SqlClient Data Provider).  In the post, I discussed using the first Connection Manager (based on the OLE DB provider) within Data Flow tasks and using the second Connection Manager (based on the ADO.Net provider) for Execute SQL tasks (since it is easier to work with parameterized queries like calls to stored procedures).  I still stand by that tip and continue to do that in just about every package I develop to this day (perhaps that will change in SSIS 2012 – I just haven’t made that leap yet on with my current project).

So, in keeping with the naming aspect of this tip, I suggest naming your Connection Managers using two-part names – the first part reflects the database name and the second part reflects the provider being used.  Thus, in the example above, if that Connection Manager originally created was based on an OLE DB provider, I would name it FinMart.OLEDB.  And then I would add a second Connection Manager to the same database using an ADO.Net provider and name it FinMart.ADONET.  So my package would have these two (at least) Connection Managers:

image

By having both Connection Managers in place, I can easily know which one to use for which task and have the best of both worlds (working in Data Flows and with the Execute SQL task…)

Happy SSIS’ing!!

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

Tuesday, July 31, 2012

My Favorite SSIS Tips and Tricks–Tip #1

As part of SQL Saturday #122 in Louisville, I had the pleasure of doing a presentation on my favorite SSIS tips and tricks.  All of the attendees seemed to enjoy the topic and hopefully learned of a few tips and tricks that they might not have known about.  The success of that session led me to thinking that I should do a series of tips and tricks blog posts.  Thus, here’s the first of many such posts that I’ll be doing over the course of the next few months or so.  Feel free to comment if you find any of these useful or if you question their usefulness (hey, I’m sure I don’t know about every tip or trick out there!)  And yes, I know most of these are likely mentioned in some other blog post or website somewhere, but hopefully reiterating them here will drive home the point!

So, my first tip is to get and use BIDS Helper.  For those SSIS developers out there who are not familiar with it, BIDS Helper is a great add-on that provides a whole set of additional features for the different BIDS (and now SSDT) designers.  Of course, being an SSIS developer, the features related to SSIS package design are of particular interest to me.  Truth be told, I just will not develop without having BIDS Helper installed – it’s the first thing I download and add once I’ve installed SQL Server on a new machine.  And even though SSIS 2012 and the new design interface in SSDT add some of the features that BIDS Helper introduced several years ago, the other features included with BIDS Helper that are not in SSIS 2012/SSDT are still extremely useful, so anyone doing development with the newest version of SQL Server will still want to get BIDS Helper and use it religiously.  So take a look, get it, install it, and love it!!

PS.  Kudos and a thousand thanks to the development team behind BIDS Helper – the SSIS community owes you all a debt of gratitude!!